Sequence purchases in a table where the sequence is unique to the purchaser

Question

I'm looking at a table as follows:

Idx | CustID |Item       | Date
________________________________
1   | 1      | Black Ink | 2023-01-01
2   | 2      | Red Ink   | 2023-01-21
3   | 1      | Black Ink | 2023-01-12
4   | 2      | Red ink   | 2023-02-14

What I'm trying to do is to sequence the purchases by customer with a unique sequence per customer as follows:

Idx | CustID |Item       | Date       | Sequence
_________________________________________________
1   | 1      | Black Ink | 2023-01-01 | 1
3   | 1      | Black Ink | 2023-01-12 | 2
2   | 2      | Red Ink   | 2023-01-21 | 1
4   | 2      | Red ink   | 2023-02-14 | 2

How do I add the 'Sequence' column? I've tried using a sub-select statement with ROW_NUMBER() OVER (ORDER BY CustID, Item) but it just sequences the entire table like Idx.

Answer 1

You need to add PARTITION BY clause to your ROW_NUMBER() to start numbering againg for new CustID

SELECT *, ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY Item) rn
FROM MY_TABLE