Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 simple MAX question Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, January 14, 2013 8:21 AM
 Grasshopper 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?thanksPete
Post #1406759
 Posted Monday, January 14, 2013 8:32 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, August 28, 2013 2:37 AM Points: 5,075, Visits: 4,834
 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`
Post #1406764
 Posted Monday, January 14, 2013 8:34 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 2:23 PM Points: 10,854, Visits: 10,012
 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 Group: General Forum Members Last Login: Today @ 9:00 AM Points: 5,324, Visits: 21,913
Post #1406768
 Posted Saturday, January 19, 2013 10:15 AM
 Grasshopper 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.ThanksPete
Post #1409215
 Posted Wednesday, February 13, 2013 5:07 PM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 5:23 AM Points: 4,570, Visits: 8,297
 Pete-600513 (1/19/2013)Thank you very much. I thought there might be an easy way with the max functionYou were right.`SELECT DT.max_score, A.name, a.dobFROM dbo.Exam A INNER JOIN (SELECT MAX(score) max_score FROM dbo.Exam ) DT ON DT.max_score = A.score `
Post #1419776

 Permissions