CODEWITHSUNDEEP
sqlgreatest-n-per-groupwindow-functionsgaps-and-islands
aobasier
aobasier

Reputation: 13

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

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!

Upvotes: 0

Views: 1235

Answers (2)

shawnt00
shawnt00

Reputation: 17915

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;

Upvotes: 0

GMB
GMB

Reputation: 222502

This is a gaps-and-islands problem. Here is one approach using window functions: the idea is to use a window sum that increments everytime the "first" occurence of a product is seen:

select t.*, 
    sum(case when rn = 1 then 1 else 0 end) over(order by rownum) new_rownum
from(
    select t.*, row_number() over(partition by product order by rownum) rn
    from mytable t
) t
order by rownum

Upvotes: 3

Related Questions