I have a table like the following:
group_id sub_group_id user_id score time 1 a1 ann 1 2019 1 a1 bob 1 2020 1 a2 cat 0 2020 2 b1 dan 0 2019 2 b1 eva 0 2019 2 b1 ed 1 2020 2 b2 liz 1 2020
i want to rank user_id within subgroup of each group by the score and then by time (earlier better) each user_id gets. so the desired output is
group_id sub_group_id user_id score time rank 1 a1 ann 1 2019 1 1 a1 bob 1 2020 2 1 a2 cat 0 2020 1 2 b1 dan 0 2019 1 2 b1 eva 0 2019 1 2 b1 ed 1 2020 2 2 b2 liz 1 2020 1
Advertisement
Answer
Use rank()
:
select t.*, rank() over (partition by group_id, sub_group_id order by score desc, time) as ranking from t;
Actually, I’m not sure if higher scores are better than lower ones, so you might want score asc
.