Skip to content
Advertisement

partition by customer for distinct items

enter image description here

select customer_id, row_number()over(partition by customer id order by date) as rn from table

enter image description here

How to get same rn when Item Id is the same?

Below did not work: #1 select customer_id, row_number()over(partition by customer id, Item Id order by date) as rn from table

Advertisement

Answer

We can try to use DENSE_RANK instead of row_number window function

If the optional PARTITION BY clause is present, the rankings are reset for each group of rows. Rows with equal values for the ranking criteria receive the same rank

select customer_id, DENSE_RANK() over(partition by customer id order by date) as rn 
from table
10 People found this is helpful
Advertisement