Maddy Mc - 1 year ago 67

SQL Question

`+---+------------+`

| 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.