Numbering rows from 1 to N based on a column value-CodePudding

Sample:

id  value  
1   a  
1   b  
1   c  
1   d  
1   a  
1   b  
1   d  
1   a

Expected outcome:

id  value   outcome  
1   a   1  
1   b   1  
1   c   1  
1   d   1  
1   a   2  
1   b   2  
1   d   2  
1   a   3

So the basic idea is that I need to number the rows I have based on the value column - whenever it reaches "d", the count starts over. Not sure which kind of window function I'd use do to that, so any help is appreciated! Thanks in advance!

CodePudding user response：

Use row_number window function with partition by value or by id and value (based on desired output):

-- sample data
with dataset(id, value) as(
    values (1, 'a'),
        (1, 'b'),
        (1, 'c'),
        (1, 'd'),
        (1, 'a'),
        (1, 'b'),
        (1, 'd'),
        (1, 'a')
)

-- query
select *,
    row_number() over (partition by id, value) -- or (partition by value)
from dataset;

Note that if there is no column which will allow "natural" ordering for the over clause (i.e. over (partition by id, value order by some_column_like_timestamp)) then the actual order is not guaranteed between queries (you will be able to observe it if there are other columns present which has different values in the same partition).

CodePudding user response：

Use row_number to give them a unique number, then order by row_number and value.

select
  *,
  row_number() over ( partition by (val) ) as rn
from stuff
order by rn, val;

Demonstration