CODEWITHSUNDEEP
sqldb2ibm-midrange
Zo Has
Zo Has

Reputation: 13038

Row number only for matching rows by id?

I have table structure like

Category EmpName
   1      Harry
   1      John
   1      Ford 
   2      James
   2      Mark  
   2      Shane 
   3      Oliver 
   3       Ted

I want results like

Category EmpName RowNumber
   1      Harry      1
   1      John       2 
   1      Ford       3
   2      James      1
   2      Mark       2
   2      Shane      3
   3      Oliver     1
   3      Ted        2

I am using db2 and row_number() is not working for different groups of records.

Upvotes: 2

Views: 1926

Answers (1)

jlnorsworthy
jlnorsworthy

Reputation: 3974

I've never used DB2, but based on my googling, it looks like the row_number() function does support the partition by clause.

Try this:

select category, empname, row_number() over(partition by category)

Upvotes: 4

Related Questions