CODEWITHSUNDEEP
sqldb2row-number
MISNole
MISNole

Reputation: 1062

Use Row_Number To Number Records in DB2 (And Reset Count on Change of ID)

What I'd like to do is number the records the query returns, but reset the number to 1 on the change of a particular field. For instance, I would like the Row_Number count to reset back to 1 each time DCACCT changes but increment by 1 when there are multiple records for DCACCT.

The new column would ideally look like this (where the first number is the row_count and the second column is DCACCT): 1 - 11

1 - 13

1 - 14

1 - 18

1 - 24

2 - 24

1 - 27

1 - 28

1 - 29

2 - 29

1 - 33

1 - 39

2 - 39

3 - 39

4 - 39

1 - 40

DB2 Query Results

I'm trying to use the Row_Number function, but I am receiving an error of Function not supported for this query but I'm not sure what I am doing wrong here. Can I accomplish my goal using this function?

SELECT
Row_Number() Over (Order By DCACCT ASC) as row_num,
COMM.DCACCT, COMM.DCATYP, COMM.DCCODE, COMM.DCDESC
FROM P50DATA.DCMTRNL5 COMM
JOIN P50DATA.PACNTSL1 PAT ON COMM.DCACCT=PAT.PACTNO
WHERE
PAT.PLAST NOT IN ('SRC','WELL','EMERGENCY','CONFIRM')
AND COMM.DCCODE IN ('PAT1','PAT2','PAT3','PAT4','PINS','PTRX','MRGF')
ORDER BY COMM.DCACCT;

Upvotes: 1

Views: 2726

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You are looking for the partition by clause:

select Row_Number() Over (partition By DCACCT order by DCACCT) as row_num,

You might have a preference for the column used for ordering within each DCACCT value -- such as a creation date or id -- but this just uses the field itself for an arbitrary ordering.

Upvotes: 2

Related Questions