Group sequential repeated values sqlite

Question

I have data that repeated sequentially..

A
A
A
B
B
B
A
A
A

I need to group them like this

A
B
A

What is the best approach to do so using sqlite?

Answer 1

Assuming that you have a column that defines the ordering of the rows, say id, you can address this gaps-and-island problem with window functions:

select col, count(*) cnt, min(id) first_id, max(id) last_id
from (
    select t.*,
        row_number() over(order by id) rn1,
        row_number() over(partition by col order by id) rn2
    from mytable t
) t
group by col, rn1 - rn2
order by min(id)

I added a few columns to the resultset that give more information about the content of each group.

Answer 2

If you have defined a column that defines the order of the rows, like an id, you can use window function LEAD():

select col
from (
  select col, lead(col, 1, '') over (order by id) next_col
  from tablename
)
where col <> next_col

See the demo.
Results:

| col |
| --- |
| A   |
| B   |
| A   |