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: Thursday, September 1, 2016 2:56 AM Points: 5,969, Visits: 6,067
 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
 SSCoach Group: General Forum Members Last Login: Tuesday, December 6, 2016 8:08 PM Points: 16,145, Visits: 16,850
 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: Sunday, January 4, 2015 7:55 AM Points: 5,333, Visits: 25,280
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
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 11:41 AM Points: 5,676, Visits: 11,187
 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