3

There is a table which has 4 columns: id, student_name,phone_num, score. And I want to select the TOP 3 student base on the score.

table:

id|student_name  |phone_num|score

1 | James        | 001350  | 89

2 | Roomi        | 123012  | 78

3 | Sibay        | 123012  | 65

4 | Ellae        | 123012  | 78

5 | Katee        | 123012  | 33

As the table shows, there are two students have the same scores. So they are in the same rank.

I tried to use 'LIMIT' but it can only select 3 rows.

SELECT id,student_name,score
FROM table
GROUP BY id,student_name,score
ORDER BY score
LIMIT 3

Expected results:

id|student_name  |score

1 | James        | 89

2 | Roomi        | 78

4 | Ellae        | 78

3 | Sibay        | 65

Thanks!

  • 1
    AFAIK there is no LIMIT keyword in SQL Server. Are you sure you tagged this question correctly? – ADyson Apr 17 at 15:22
6

You'll want to use a ranking function - I'd recommend Dense Rank.

; with CTE as 
    (Select ID, Student_Name, Score, Dense_Rank() over (order by score desc) as DR
    From Table)

Select *
from CTE
where DR <= 3

To expand on this function:

Dense_Rank will assign tied values the same number, then assign the next highest value the next highest number (in comparison to Rank, which will skip ranks if there are ties). For example:

Value  Rank  Dense_Rank
1      1     1
3      2     2
3      2     2
7      4     3
8      5     4
6

You want to use DENSE_RANK here:

WITH cte AS (
    SELECT id, student_name, score,
        DENSE_RANK() OVER (ORDER BY score DESC) dr
    FROM yourTable
)

SELECT id, student_name, score
FROM cte
WHERE dr <= 3
ORDER BY score DESC;

enter image description here

Another way, using a subquery to find the top 3 distinct highest scores:

SELECT id, student_name, score
FROM yourTable
WHERE score IN (SELECT DISTINCT TOP 3 score FROM yourTable ORDER BY score DESC)
ORDER BY score DESC;

This second approach is similar to what you were trying to do. Here is a demo for the second query:

Demo

  • Was just getting ready to post the same thing and you beat me by a few seconds :) – Chris Hackett Apr 17 at 15:19
  • Yeah, I guess there is one canonical way to solve this...right down to how you alias it, haha. – APH Apr 17 at 15:20
  • Thank you, sir, both of the answers are helpful for me. But @APH answer this problem first so I mark his/her answer as accepted. Appreciate for your help! – user8400129 Apr 19 at 2:13
  • @user8400129 No, I answered first actually :-) – Tim Biegeleisen Apr 19 at 3:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.