Click here to Skip to main content
15,920,596 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I have the below student table with rows like this

C#
student_id  subject_id     Mark
1               1                 60
1               2                 55
1               3                 70
2               1                 80      
2               2                 40
2               3                 50
3               1                 30      
3               2                 40
3               3                 60




I need to find the student_id who has got maximum total marks.Could anyone please help.

Thanks

What I have tried:

I tried with using max(sum) but did not get the correct result.
Posted

Something like-
SQL
SELECT MAX(TotalMarks) AS TotalMarks
FROM (
    SELECT student_id,SUM(Mark) AS TotalMarks
    FROM Student
    GROUP BY student_id
) AS TBL

There could be better way to do this but it should gie you the desired result.

Hope, it helps :)
 
Share this answer
 
v2
This could be done several ways. One quite simple way is to use a query to group and sort the student's based on their marks, for example
SQL
SELECT a.Student_ID, SUM(a.Mark) AS Total
      FROM Student a
      GROUP BY a.Student_ID
      ORDER BY Total DESC

This would give you
STUDENT_ID   TOTAL
----------   -----
1            185
2            170
3            130

Now to pick the first one, you can use the result as an inline view and limit the number of rows to 1. For example
SQL
SELECT *
FROM (SELECT a.Student_ID, SUM(a.Mark) AS Total
      FROM Student a
      GROUP BY a.Student_ID
      ORDER BY Total DESC
     ) b
WHERE RowNum <= 1;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900