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?
acsakanyAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
select * from
(
select ranking  = dense_rank() over(partition by wineid order by total desc), *
from tablename
) a
where ranking = 1
0
 
ErnariashCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.