CODEWITHSUNDEEP
sqloracle-database
user2932820
user2932820

Reputation: 11

Count rows and nth occurence of a unique transaction

I have a query that returns the following:

CLA_CASE_NO ITEM_ID
1021321 9056580
1021321 9090998
1021321 9223063
1021321 9526427
1021549 9223054
1021549 9526380
1021704 9538932
1021704 9538933
1021704 9538934
1021726 9223408
1021726 9526379

However I would like it to return the following:

CLA_NO  ITEM_ID NTH COUNT
1021321 9056580 1
1021321 9090998 2
1021321 9223063 3
1021321 9526427 4
1021549 9223054 1
1021549 9526380 2
1021704 9538932 1
1021704 9538933 2
1021704 9538934 3
1021726 9223408 1
1021726 9526379 2

I would like it to count based on the sequential order of the item_id Any help would be greatly appreciated.

Thanks

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

You are looking for the row_number() analytic function:

select CLA_NO, ITEM_ID, 
       row_number() over (partition by cla_no order by item_id) as nth
from q

Upvotes: 3

Related Questions