CODEWITHSUNDEEP
t-sql
John F
John F

Reputation: 1072

SQL - add sequential counter column starting at condition

I have a table:

  id   market
  1    mkt1
  2    mkt2
  3    mkt1
  4    special
  5    mkt2
  6    mkt2
  7    special

How can I select all columns from the table while also adding a sequential counter column, which starts counting once a condition has been triggered? In this example, when market=="special":

 id   market  count
 1    mkt1     0
 2    mkt2     0
 3    mkt1     0
 4    special  1
 5    mkt2     2
 6    mkt2     3
 7    special  4

Upvotes: 2

Views: 171

Answers (1)

sgeddes
sgeddes

Reputation: 62841

Here's one option using row_number with union all:

with cte as (
  select min(id) as id from t where market = 'special'
)
select t.id, t.market, 0 rn
from t join cte on t.id < cte.id
union all
select t.id, t.market, row_number() over (order by t.id) rn
from t join cte on t.id >= cte.id

Edited to use min after your edits...

Upvotes: 1

Related Questions