I want the count adding one only when the value has not been show before. The base table is:
rownum product 1 coke 2 coke 3 burger 4 burger 5 chocolate 6 apple 7 coke 8 burger
The goal is:
rownum product 1 coke 1 coke 2 burger 2 burger 3 chocolate 4 apple 4 coke 4 burger
I am thinking to compare the current row with all previous rows, but I have difficulty to call all previous rows. Thank you!
Advertisement
Answer
Several different ways to accomplish this. I guess you’ll get to pick one you like the best. This one just finds the first row number per product. You then just need to collapse the holes with an easy application of dense_rank()
to the initial grouping.
with data as ( select *, min(rownum) over (partition by product) as minrow from T ) select dense_rank() over (order by minrow) as rownum, product from data order by rownum, data.rownum;