OTARIKI - 1 month ago 11

SQL Question

Given table/data like this:

`WITH T(col1, col2) AS(`

SELECT 1, 'A' FROM DUAL

UNION ALL

SELECT 2, 'B' FROM DUAL

UNION ALL

SELECT 3, 'B' FROM DUAL

UNION ALL

SELECT 4, 'B' FROM DUAL

UNION ALL

SELECT 5, 'A' FROM DUAL

UNION ALL

SELECT 6, 'B' FROM DUAL

UNION ALL

SELECT 7, 'B' FROM DUAL

UNION ALL

SELECT 8, 'A' FROM DUAL

)

I need get

`col2`

`col2`

`col1`

Result must be:

`col1 | col2 | queue_count`

-------------------------

1 |A |1

2 |B |3

3 |B |3

4 |B |3

5 |A |1

6 |B |2

7 |B |2

8 |A |1

I tried some analytic functions but don't achieve desirable result.

It's possible to do this in "pure" SQL? without using pl/sql (without looping and each row step by step handling, etc.)

Answer

This is a gap and islands problem. Here is one way to solve it:

```
select col1, col2,
count(*) over (partition by col2, seqnum - seqnum_col2) as queue_count
from (select t.*,
row_number() over (partition by col2 order by col1) as seqnum_col2,
row_number() over (order by col1) as seqnum
from t
) t;
```

It is a little hard to explain why this works. However, if you run the subquery and stare at the results, you will probably "get" why the difference between the row numbers works to identify adjacent rows with the same value in `col2`

.