Maddy Mc Maddy Mc - 1 year ago 76
SQL Question

How to generate sequence like

+---+------------+
| V | output |
+---+------------+
| y | 1 |
| y | 2 |
| y | 3 |
| N | 0 |
| y | 1 |
| y | 2 |
| N | 0 |
| N | 1 |
+---+------------+

Answer Source

Let me assume that you have a column (say, id) that has the ordering information. Then, you want to identify groups of "Y"s and "N"s that appear together and then enumerate them.

You can do this using a difference of row numbers trick:

select t.v,
       row_number() over (partition by v, seqnum_id - seqnum_vid order by id) as output
from (select t.*,
             row_number() over (order by id) as seqnum_id,
             row_number() over (partition v by order by id) as seqnum_vid
      from t
     ) t;

Explaining how this works is usually tricky. I recommend that you run the subquery to see what the sequence numbers look like and why the difference is constant for the groups you want to identify.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download