SQL Getting row number only when the value is different from all previous values

Question

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!

Answer 1

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;