Sample data:
student | marks | subject |
---|---|---|
stud1 | 100 | sub1 |
stud1 | 400 | sub2 |
stud1 | 500 | sub3 |
stud2 | 200 | sub1 |
stud2 | 700 | sub2 |
stud2 | 800 | sub3 |
stud2 | 900 | sub4 |
stud3 | 300 | sub1 |
stud3 | 600 | sub2 |
stud4 | 1000 | sub1 |
Trying to partition by student and order by sum of marks like below using dense_rank()
. There are multiple other columns and since the data is big, trying to avoid any joins here.
Expected output:
student | marks | subject | ds_rnk |
---|---|---|---|
stud1 | 100 | sub1 | 2 |
stud1 | 400 | sub2 | 2 |
stud1 | 500 | sub3 | 2 |
stud2 | 200 | sub1 | 1 |
stud2 | 700 | sub2 | 1 |
stud2 | 800 | sub3 | 1 |
stud2 | 900 | sub4 | 1 |
stud3 | 300 | sub1 | 3 |
stud3 | 600 | sub2 | 3 |
stud4 | 1000 | sub1 | 2 |
Thanks in advance!
CodePudding user response:
You can calculate sum and dense_rank in two stages:
with cte as (
select t.*, sum(marks) over (partition by student) as sum_marks
from t
)
select cte.*, dense_rank() over (order by sum_marks desc) as dense_rankk
from cte