CODEWITHSUNDEEP
mysql
Nandhini
Nandhini

Reputation: 15

List of Id's top value row Lists

I have a table like this

Student_id mark semester
001 50 2
002 80 2
001 52 3
002 78 3

I want a query to get each student_id 's highest mark row.

excepted output :

Student_id mark semester
002 80 2
001 52 3

Upvotes: 0

Views: 36

Answers (2)

zeeshan12396
zeeshan12396

Reputation: 412

Partition the data and rank them in descending order , extract result which having rank 1 its in sqlserver

WITH CTE AS(
SELECT ROLL,MARK,rank() OVER (PARTITION BY ROLL ORDER BY MARK desc)[rank],SEM    FROM #NEW)
select roll,mark,sem from cte where rank=1 
--sqlserver

Upvotes: 1

adammak
adammak

Reputation: 78

Just use GROUP BY Student_id to be able to retrieve the max mark.

SELECT Student_id, MAX(mark) as mark, semester FROM grades GROUP BY student_id

If you want to sort the marks in descending order, add ORDER BY mark DESC to the end of the query.

Upvotes: 1

Related Questions