## simple MAX question

 Author Message Pete-600513 SSC-Enthusiastic Group: General Forum Members Points: 132 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 SSC Guru Group: General Forum Members Points: 62864 Visits: 8598 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` How to post data/code for the best help - Jeff ModenNeed a string splitter, try this - Jeff ModenHow to post performance problems - Gail ShawManaging Transaction Logs - Gail ShawTroubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger Sean Lange SSC Guru Group: General Forum Members Points: 148695 Visits: 18575 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 Modens 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 SSC-Dedicated Group: General Forum Members Points: 34827 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 SSC-Enthusiastic Group: General Forum Members Points: 132 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 SSC Guru Group: General Forum Members Points: 59241 Visits: 12977 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 `