(SQL) Renumber unique ids in a table-CodePudding

I have receipt data with receipt numbers like this:

receipt_id  item_id
123         321
123         322
123         323
124         321
124         322
125         321
125         323

I need to renumber the receipt_id so it looks like this:

receipt_id  item_id
1           321
1           322
1           323
2           321
2           322
3           321
3           323

row_over or row_over partition by does not seem to get me what I want. I can do this by selecting distinct ids, renumber them, and then join them back. But there has to be a simpler, more correct way, or?

CodePudding user response：

I think you can use DENSE_RANK() for this:

WITH T(receipt_id, item_id) AS(
    VALUES
    (123,         321),
    (123,         322),
    (123,         323),
    (124,         321),
    (124,         322),
    (125,         321),
    (125,         323)
)
SELECT  DENSE_RANK() OVER(ORDER BY receipt_id) AS  receipt_id, item_id
FROM T