Search code examples
sqlgaps-and-islands

Selecting records that appear several times in a row

My problem is that I would like to select some records which appears in a row. For example we have table like this:

x
x
x
y
y
x
x
y

Query should give answer like this:

x   3
y   2
x   2
y   1
Solution

  • SQL tables represent unordered sets. Your question only makes sense if there is a column that specifies the ordering. If so, you can use the difference-of-row-numbers to determine the groups and then aggregate:

    select col1, count(*)
from (select t.*,
             row_number() over (order by <ordering col>) as seqnum,
             row_number() over (partition by col1 order by <ordering col>) as seqnum_2
      from t
     ) t
group by col1, (seqnum - seqnum_2)