How to calculate rank using SQL trick?

Suppose you have data as shown below in a table. It contains marks obtained by students in different subjects. Here, student_id and subject_id makes the primary key of the table.

 

student_id	subject_id	obtd_marks	rank
5	2	99.1	0
7	2	98.1	0
8	2	98.1	0
1	2	92.5	0
2	2	92.1	0
3	2	91.1	0
4	2	85.1	0
6	2	56.1	0
8	9	98.1	0
2	9	91.1	0

 

At first, rank is 0 for all. Now, you have to write a SQL query to rank the students on the basis of their obtained marks. Here is the query.

 

update      tbl_marks

set   rank  = (

      select      count('1')

      from  tbl_marks b

      where a.subject_id = b.subject_id

      and   b.obtd_marks > a.obtd_marks

      ) + 1

from  tbl_marks a

 

where tbl_marks is the name of table.

 

This query considers the case of multiple students having same marks giving them same rank. It also considers ranking of different subjects separately. The output thus becomes as shown below. Note that, student 7 and 8 have same rank in subject 2.

 

student_id	subject_id	obtd_marks	rank
5	2	99.1	1
7	2	98.1	2
8	2	98.1	2
1	2	92.5	4
2	2	92.1	5
3	2	91.1	6
4	2	85.1	7
6	2	56.1	8
8	9	98.1	1
2	9	91.1	2