Consider we have table as follows:
|
Studentid
|
Subjectid
|
ObtainedScore
|
|
1
|
1
|
25
|
|
2
|
1
|
45
|
|
3
|
1
|
27
|
|
4
|
1
|
35
|
|
5
|
1
|
25
|
|
6
|
1
|
24
|
|
1
|
2
|
31
|
|
2
|
2
|
42
|
|
3
|
2
|
42
|
|
4
|
2
|
36
|
|
5
|
2
|
12
|
|
6
|
2
|
10
|
If we need to rank students based on their obtained score in each subject what would you write a query for. Among many way I’m illustrating following code for generating rank using row_number(), over and partition:
Select studetid, subjected, obtainedscore, row_number() over () partition by subjectid order by obtainedscore desc) as rank
Here partition groups the table by subjectid column and generates a rank by descending order of obtainedscore.
Output of this query would be as below
|
Studentid
|
Subjectid
|
ObtainedScore
|
rank
|
|
1
|
1
|
25
|
4
|
|
2
|
1
|
45
|
1
|
|
3
|
1
|
27
|
3
|
|
4
|
1
|
35
|
2
|
|
5
|
1
|
12
|
6
|
|
6
|
1
|
24
|
5
|
|
1
|
2
|
31
|
4
|
|
2
|
2
|
42
|
2
|
|
3
|
2
|
43
|
1
|
|
4
|
2
|
36
|
3
|
|
5
|
2
|
12
|
5
|
|
6
|
2
|
10
|
6
|
serenians 