CODEWITHSUNDEEP
sql
Ng Phu Tin
Ng Phu Tin

Reputation: 13

find each student's highest score

I have this table contains student id and their score for each

|student id  |score|
|    aac     |  3  |
|    aaa     |  6  |
|    aac     |  5  |
|    aaa     |  7  |
|    aad     |  3  |

I want to find the highest score for each student. How do I do it?

I tried going through every student ID on the list but it is not efficient.

Upvotes: 0

Views: 2251

Answers (2)

zealous
zealous

Reputation: 7503

You can use window function row_number

select
  student_id,
  score
from
(
  select
    *,
    row_number() over (partition by student_id order by score desc) as rn
  from yourTable
) subq
where rn = 1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

For the exact table you gave, a simple group by query should work:

SELECT student_id, MAX(score) AS max_score
FROM yourTable
GROUP BY student_id;

Upvotes: 5

Related Questions