asked on

SQL TOP 1

I have a table with the following information

WINE_ID PRICE_BRACKET TOTAL

1 3 6
1 5 4
1 9 1
2 7 8
2 6 2
2 2 3
3 1 10
3 2 6
3 4 1

How would I write an SQL query that would pick the price_bracket for each wine with the largest total?

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ernariash

If you like subqueries try this query. thanks,

SELECT * 
FROM WINETABLE  W
WHERE EXISTS (
	SELECT 1 FROM (SELECT WINE_ID, MAX(TOTAL) MTOTAL
					FROM WINETABLE 
					GROUP BY WINE_ID
				) M
	WHERE 	M.WINE_ID = W.WINE_ID AND 	M.TOTAL = W.MTOTAL	
)

Open in new window