yogeshwar_misal yogeshwar_misal - 2 months ago 6
SQL Question

Enumerate records in table on multiple colummns

ID descr points
1000 24 100
1000 24 40
1000 25 100
1000 25 40
2000 24 100
2000 25 100
2000 26 100


Above is my table. I want to add/update column enumerating records on basis of ID and descr. How can I do that?

Below is the result I am looking for.

ID descr points order#
1000 24 100 1
1000 24 40 2
1000 25 100 1
1000 25 40 2
2000 24 100 1
2000 25 100 2
2000 26 100 3

Answer

You can use the ANSI standard function `row_number():

select id, descr, points,
       row_number() over (partition by id, descr order by points desc) as ordernum
from t;