?
Solved

SQL TOP 1

Posted on 2008-11-11
2
Medium Priority
?
664 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:acsakany
2 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 200 total points
ID: 22931642
select * from
(
select ranking  = dense_rank() over(partition by wineid order by total desc), *
from tablename
) a
where ranking = 1
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22931808
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Integration Management Part 2

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question