-1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Seamus
  • 71
  • 1
  • 1
  • 6

2 Answers2

1

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.

Your row_number() needs a partition by clause, so that each customer gets its own iterator. So something like:

select t.*,
    row_number() over(partition by custid order by idx) seq
from mytable t

Depending on your actual use case, you might want to consider ordering by date rather than by idx. Also if there may be ties, then rank() is more relevant than row_number().

GMB
  • 216,147
  • 25
  • 84
  • 135
0

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
markalex
  • 8,623
  • 2
  • 7
  • 32