CODEWITHSUNDEEP
sqloracle-database
Learner
Learner

Reputation: 11

How can I rank the consecutive records using oracle sql?

For example, for the below data set:

SUBJECT DATE   MARKS
======= =====  =====
     A  10/01  10
     B  10/02  20
     B  10/03  30
     B  10/04  30
     C  10/05  10 
     C  10/06  20
     C  10/07  20
     C  10/08  20

I need another column, Level, as shown below.

SUBJECT DATE   MARKS level
======= =====  ===== =====
     A  10/01  10    1
     B  10/02  20    1
     B  10/03  30    1
     B  10/04  30    2
     C  10/05  10    1
     C  10/06  20    1
     C  10/07  20    2
     C  10/08  20    3
     C  10/09  20    4
     C  10/10  30    1

In other words, which ever consecutive combination is repeating we have to increase the counter for that column. Can this be achieved?

Upvotes: 1

Views: 153

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You are looking for row_number():

select t.*,
       row_number() over (partition by subject, marks order by date) as level
from t
order by subject, date;

Upvotes: 3

Related Questions