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 |
CodePudding user response：
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.
CodePudding user response：
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