Sequential numbering of data sequence

Question

I do have the following table:

create table test_seq (id int, obs int);
insert into test_seq values (1,1);
insert into test_seq values (2,1);
insert into test_seq values (3,1);
insert into test_seq values (4,0);
insert into test_seq values (5,0);
insert into test_seq values (6,1);
insert into test_seq values (7,1);
insert into test_seq values (8,0);
insert into test_seq values (9,0);
insert into test_seq values (10,1);
insert into test_seq values (11,0);

Is there s SQL way, how to create the following output?

id   obs    seq_num
 1     1          1
 2     1          1
 3     1          1
 4     0          2
 5     0          2
 6     1          3
 7     1          3
 8     0          4
 9     0          4
10     1          5
11     0          6

seq_num is increased by 1 every time when value in column obs is changed compared to the previous row (ordered by id). I can solve this easily in Excel (using simple if formula), but can't figure out this in postgres.

see similar answer on stack http://stackoverflow.com/questions/3397121/how-to-show-row-numbers-in-postgresql-query — xQbert, Jan 23 '13 at 13:24

score 2 · Accepted Answer · answered Jan 23 '13 at 13:23

2

using analytic functions, something like :

select id, obs, sum(cnt) over (order by id) as seq_num
from (
  select id, obs, case when obs <> (lag(obs) over (order by id)) then 1 else 0 end as cnt
  from test_seq
)
order by id;

answered Jan 23 '13 at 13:23

Florin Ghita

17,525
6
57
76

score 1 · Answer 2 · answered Jan 23 '13 at 13:23

I've figured it out:

with t as (
    select
        id,
        obs, 
        case when lag(obs,1) over (order by id) <> obs then 1 else 0 end as test 
    from 
        tmp.test_seq
    ) 

select 
    *,
    sum(test) over (order by id rows between unbounded preceding and current row) + 1
from 
    t

score 0 · Answer 3 · answered Jan 23 '13 at 13:18

0

You can use rownumber to do that.

answered Jan 23 '13 at 13:18

Miguel Matos

191
7

Please explain how I can use row_number() to do this. – Tomas Greif Jan 23 '13 at 13:24

Sequential numbering of data sequence

3 Answers3