Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

simple MAX question Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 8:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 1:18 PM
Points: 12, Visits: 24
Hi there, I would like to find the MAX (score for example) and return who got that score. Imagine if I have a table of 3 fields: score, name and dob. How Do I return who has got the max score and their dob?
thanks
Pete
Post #1406759
Posted Monday, January 14, 2013 8:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:54 AM
Points: 5,149, Visits: 4,961
That would be one for a ranking function. Depending on how you want to do what you need to do and then also what you want to do if the 2 people have the max score.

ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()

Each behaves differently.

DECLARE @Table TABLE (Name CHAR(3), DOB DATE, Score INT)
INSERT INTO @Table VALUES ('Ant',GETDATE(),10),('Bob',GETDATE()-1,10),('Mum', GETDATE()-2,5)
SELECT * FROM @Table

;with RowNumber as
(
SELECT ROW_NUMBER() OVER(ORDER BY Score DESC) AS RowNum, Name, DOB, Score FROM @Table
)
select * from RowNumber where RowNum = 1

;with ranking as
(
SELECT RANK() OVER(ORDER BY Score DESC) AS RowNum,Name, DOB, Score FROM @Table
)
SELECT * from ranking where RowNum = 1

;with dense_ranking as
(
SELECT DENSE_RANK() OVER(ORDER BY Score DESC) AS RowNum,Name,DOB,Score FROM @Table
)
SELECT * FROM dense_ranking WHERE RowNum = 1

;with ntileing as
(
SELECT NTILE(2) OVER(ORDER BY Score DESC) AS RowNum, Name, DOB, Score FROM @Table
)
SELECT * FROM ntileing WHERE RowNum = 1





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1406764
Posted Monday, January 14, 2013 8:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
For an in-depth look at ranking functions check out BOL. http://msdn.microsoft.com/en-us/library/ms173454.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1406767
Posted Monday, January 14, 2013 8:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:58 PM
Points: 5,575, Visits: 24,827
Sounds like a homework question .. in case it is NOT ... please post the table definition(s), sample data and required results.

You can do this quickly and very easily..... click on the first link in my signature block and read the article. Now the article contains all the necessary code for table definition, creating sample data and last but not least the required results.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1406768
Posted Saturday, January 19, 2013 10:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 1:18 PM
Points: 12, Visits: 24
Thank you very much. I thought there might be an easy way with the max function, but it looks like I need to use a different function...so will go and do some research.
Thanks
Pete
Post #1409215
Posted Wednesday, February 13, 2013 5:07 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:39 PM
Points: 4,576, Visits: 8,342
Pete-600513 (1/19/2013)
Thank you very much. I thought there might be an easy way with the max function


You were right.

SELECT DT.max_score, A.name, a.dob
FROM dbo.Exam A
INNER JOIN (SELECT MAX(score) max_score
FROM dbo.Exam
) DT ON DT.max_score = A.score

Post #1419776
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse