CODEWITHSUNDEEP
sqloracle-databaserow-number
Rohit Prasad
Rohit Prasad

Reputation: 145

Assign Unique Row Number based on pattern

I have a table with the following fields 

CourseID  Amount17 Amount15 Amount13
100152  3000    2400    0
100204  3000    2400    0
100066  3000    2400    0
100066  3000    2400    1800
100067  3000    2400    1800
100343  3000    2400    1800
100126  3000    2400    1800
100022  3000    3000    0
100344  3000    3000    0
100050  3000    3000    0
100078  3000    3000    0

I would like to group matching three amount columns in a list. My desired output 

 CourseID  Amount17 Amount15 Amount13   CourseList
    100152  3000    2400    0            1
    100204  3000    2400    0            1
    100066  3000    2400    0            1
    100066  3000    2400    1800         2
    100067  3000    2400    1800         2
    100343  3000    2400    1800         2
    100126  3000    2400    1800         2
    100022  3000    3000    0            3
    100344  3000    3000    0            3
    100050  3000    3000    0            3
    100078  3000    3000    0            3

I have tried row_number over(order by ) but it does not seem to help. Tried using DenseRank but of no use.

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think you just want dense_rank():

select t.*,
       dense_rank() over (order by amount17, amount15, amount13) as courselist
from t;

Upvotes: 1

Related Questions