## simple MAX question

 Author Message Pete-600513 Grasshopper Group: General Forum Members 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 anthony.green SSCertifiable Group: General Forum Members Points: 6108 Visits: 6080 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 hereHow to post data/code for the best help - Jeff ModenWhen a question, really isn't a question - Jeff SmithNeed a string splitter, try this - Jeff ModenHow to post performance problems - Gail ShawCrossTabs-Part1 & Part2 - Jeff ModenSQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola HallengrenManaging Transaction Logs - Gail ShawTroubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger Sean Lange SSCoach Group: General Forum Members Points: 16675 Visits: 17035 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) bitbucket-25253 SSCertifiable Group: General Forum Members Points: 5697 Visits: 25280 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. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read Pete-600513 Grasshopper Group: General Forum Members 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 Sergiy SSCertifiable Group: General Forum Members Points: 5852 Visits: 11412 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 `