CODEWITHSUNDEEP
sqlhive
MysticRenge
MysticRenge

Reputation: 395

Change row number after occurrence of a specific value in one column using SQL

The requirement is to number the rows as per the state-PASS and order by the time for a specific id: so when every time PASS occurs in the state for one id it should icrement the number. and if pass does not occur it should maintain the number

I am currently trying to use 

rank() over (partition by id, user, state = 'PASS' order by time)

but this works well till there is a pass alternately occuring but does not work if a pass does not occur

Any help would be appreciated. Thank you

This is the sample data: 

ID  user    State    time          Req. number
-----------------------------------------------
1    a1     work     timestamp1     1
1    f1     pass     timestamp2     1
1    s1     work     timestamp3     2
1    f1     pass     timestamp4     2
1    m1     break    timestamp5     3
1    s1     pass     timestamp6     3
1    f1     work     timestamp7     4
1    a1     pass     timestamp8     4
1    v1     work     timestamp9     5
1    s1     endwork  timestamp10    5
1    s1     endwork  timestamp11    5
1    a1     work     timestamp12    5
2    a2     work     timestamp13    1
2    s2     endwork  timestamp14    1

Upvotes: 0

Views: 288

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You seem to want one more than the cumulative number of "PASS"s up to the previous row:

select t.*,
       coalesce(sum(case when state = 'PASS' then 1 else 0 end) over
                    (partition by id
                     order by time
                     rows between unbounded preceding and 1 preceding
                    ) + 1, 1
               )
from t;

Upvotes: 2

Related Questions