CODEWITHSUNDEEP
sqlpostgresql
Julien Navarre
Julien Navarre

Reputation: 7830

Conditionally reset row_number

I would like to count my rows grouped by an id and ordered by a time value like with row_number() over(partition by id order by time). But I want to reset the row count when I encounter a special condition.

For example in the table below, when event = 'y' I would like to re-count from 1 for the same id.

id          time                      event      rank
----------- ------------------------- ---------- ----------
400         2016-07-09 11:31:30       y          1
400         2016-07-09 11:31:31       x          2
400         2016-07-09 11:31:37       x          3
400         2016-07-09 11:31:38       x          4
400         2016-07-09 11:31:42       y          1
400         2016-07-09 11:31:43       x          2
400         2016-07-09 11:31:44       x          3
400         2016-07-09 11:31:59       y          1
400         2016-07-09 11:32:43       x          2
400         2016-07-09 11:33:44       x          3
401         2016-07-09 10:31:30       y          1
401         2016-07-09 10:31:31       x          2
401         2016-07-09 10:37:37       x          3
401         2016-07-09 10:38:38       x          4
401         2016-07-09 11:05:42       y          1
401         2016-07-09 11:07:43       x          2
401         2016-07-09 11:31:44       x          3

I tried a lot of queries but nothing comes close to what I expect.

Can you help me on this please ? Thanks for any help.

Upvotes: 1

Views: 4886

Answers (1)

sstan
sstan

Reputation: 36473

You can use a conditional cumulative sum to further partition the rows by the event field:

with cte as (
  select id, time, event,
         sum(case when event = 'y' then 1 else 0 end) over (partition by id order by time) as group_id
    from tbl
)
select id, time, event,
       row_number() over (partition by id, group_id order by time) as rank
  from cte

Upvotes: 4

Related Questions