Select second and third highest scores? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Select second and third highest scores?

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-09-06 : 16:46:39

I have table Test_Scores with columns ID (int), StudentID (int), TestID (int), Score(int). How can I write a select statement that would return the second and third highest score?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-06 : 17:05:03

Do you know how to pick the top 3?

cruellogic
Starting Member

1 Post

Posted - 2013-09-06 : 17:20:45

I think this is what you are looking for.


;WITH CTE 
AS
(
SELECT ID, StudentID, TestID, Score, DENSE_RANK() OVER(ORDER BY Score DESC) AS R
FROM dbo.Test_Scores WITH(NOLOCK)
)
SELECT ID, StudentID, TestID, Score 
FROM CTE
WHERE R IN (2,3)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources