OTARIKI - 7 months ago 52
SQL Question

# Get queue repeated same values count

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`
queue repeated counts, I mean, how many times repeated same value in
`col2`
when rows are ordered by
`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.)

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

Source (Stackoverflow)