simple MAX question

Question

simple MAX question

Pete-600513

SSC Veteran

Points: 200
More actions
January 14, 2013 at 8:21 am

#393930

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?
thanks
Pete

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

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.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Pete-600513 SSC Veteran Points: 200 More actions · Answer 4

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.

Thanks

Pete

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Pete-600513 (1/19/2013)
Thank you very much. I thought there might be an easy way with the max function

You were right.

SELECT DT.max_score, A.name, a.dob

FROM dbo.Exam A

INNER JOIN (SELECT MAX(score) max_score

FROM dbo.Exam

) DT ON DT.max_score = A.score

_____________
Code for TallyGenerator