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?
Go to Top of Page

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)
Go to Top of Page
   

- Advertisement -