Click here to Skip to main content
15,903,523 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SNAME MARKS SUBJECT
SURESH 98 ENGLISH
SURESH 97 MATCHS
SURESH 96 SCIENCE
SUNIL 95 ENGLISH
SUNIL 94 MATHS
SUNIL 99 SCIENCE
RAMESH 91 ENGLISH
RAMESH 92 MATHS
RAMESH 98 SCIENCE

What I have tried:

how to calculate 1st rank student with his total gross marks ,the subject and marks which has maximum score.
Posted
Updated 10-Aug-17 19:48pm

1 solution

Do it in stages: first get the students total score and highest personal score, and get the highest scorer:
SQL
SELECT TOP 1 SNAME, SUM(Marks) As Total, MAX(Marks) As Highest
FROM MyTable
GROUP BY SName
ORDER By Total DESC
That returns one student:
SName  Total   Highest
SURESH	 291        98
Then JOIN that with the original table to get the subject as well:
SQL
SELECT s.SName, s.Total, a.Subject, s.Highest
FROM MyTable a
JOIN (SELECT TOP 1 SNAME, SUM(Marks) As Total, MAX(Marks) As Highest
        FROM MyTable
        GROUP BY SName
        ORDER By Total DESC) s 
ON s.SName = a.SName AND s.Highest = a.Marks
Result: one row, the info you need.
SName     Total   Subject  Highest
SURESH	    291   English       98

But...that is a very poor database design. You are storing the same info repeatedly, and should be using three tables: one for students, one for subjects, and one for results which uses foreign keys back to the others.
 
Share this answer
 
Comments
Maciej Los 11-Aug-17 1:50am    
What about ranking functions, such as RANK? There might be more than one person who's reached 1st place.

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