CODEWITHSUNDEEP
sql-servert-sql
James
James

Reputation: 2600

How to implement score ranking

I have a student table (sid,sname), a course table(cid,cname) and a sc table (sid,cid,score).

How to rank students information and their total score orderd by total score, if the total score same, orderd by cid.

My current SQL as below:

SELECT s.Sname,ISNULL(SUM(ss.Score),0) 
FROM Student s 
LEFT JOIN SC ss 
    ON s.Sid=ss.Sid 
GROUP BY s.Sname 
ORDER BY SUM(ss.Score) DESC

Upvotes: 0

Views: 211

Answers (1)

Aziz Shaikh
Aziz Shaikh

Reputation: 16534

If I understood you correctly, you may use the rank in your query. Try the following:

SELECT rank() OVER (ORDER BY SUM(ss.Score) DESC) as rank, s.Sname, ISNULL(SUM(ss.Score),0) 
FROM Student s LEFT JOIN SC ss 
ON s.Sid = ss.Sid 
GROUP BY s.Sname 
ORDER BY SUM(ss.Score) DESC

See working demo on SQL Fiddle.

Upvotes: 3

Related Questions