InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 23:00:23 GMT20http://www.sqlservercentral.com/Forums/Topic1406759-3077-1.aspx[quote][b]Pete-600513 (1/19/2013)[/b][hr]Thank you very much. I thought there might be an easy way with the max function[/quote]You were right.[code="sql"]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 [/code]Wed, 13 Feb 2013 17:07:32 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1406759-3077-1.aspxThank 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.ThanksPeteSat, 19 Jan 2013 10:15:30 GMTPete-600513http://www.sqlservercentral.com/Forums/Topic1406759-3077-1.aspxSounds 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.Mon, 14 Jan 2013 08:34:52 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1406759-3077-1.aspxFor an in-depth look at ranking functions check out BOL. [url=http://msdn.microsoft.com/en-us/library/ms173454.aspx]http://msdn.microsoft.com/en-us/library/ms173454.aspx[/url]Mon, 14 Jan 2013 08:34:23 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1406759-3077-1.aspxThat 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.[code="sql"]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[/code]Mon, 14 Jan 2013 08:32:42 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1406759-3077-1.aspxHi 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?thanksPeteMon, 14 Jan 2013 08:21:55 GMTPete-600513