CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
aobasier
aobasier

Reputation: 13

SQL Getting row number when the value is different from previous one, no matter whether the value shows before

Hi I have a difficulty when creating count: the base table is

rownum product   
 1      coke  
 2      coke
 3      burger
 4      burger
 5      chocolate
 6      apple
 7      coke
 8      burger

I want the result like below, as long as the product is different than the previous one, the count add one. I trying to use dense_rank(), rank() function, but it's not what I want. Thank youstrong text

rownum product   
 1      coke 
 1      coke      
 2      burger     
 2      burger     
 3      chocolate  
 4      apple      
 5      coke       
 6      burger

Upvotes: 0

Views: 445

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Use lag() to see when the value changes and then a cumulative sum:

select t.*,
       sum(case when prev_product = product then 0 else 1 end) over (order by rownum) as new_rownum
from (select t.*, lag(product) over (order by rownum) as prev_product
      from base t
     ) t

Upvotes: 2

Related Questions