CODEWITHSUNDEEP
sql
yogeshwar_misal
yogeshwar_misal

Reputation: 83

Enumerate records in table on multiple columns

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

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

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;

Upvotes: 2

Related Questions