Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE my_table (
    column1 VARCHAR(1),
    timestamp INT
);

INSERT INTO my_table (column1, timestamp) VALUES
('a', 1),
('a', 2),
('a', 3),
('b', 4),
('b', 5),
('a', 6),
('a', 7),
('c', 9);
select * from my_table;

CREATE TABLE



INSERT 0 8




column1
timestamp


a
1

a
2

a
3

b
4

b
5

a
6

a
7

c
9



SELECT 8


with cte as 
(
select * 
  , row_number() over (order by timestamp) - 
    row_number() over (partition by column1 order by  timestamp) as grp
from my_table
  )

select column1, min(timestamp) as timestamp
from cte 
group by column1, grp
order by timestamp


column1
timestamp


a
1

b
4

a
6

c
9



SELECT 4


with cte as 
(
select * 
  , lag(column1) over (order by timestamp) as lagcolumn1
from my_table
  )

select column1, timestamp
from cte 
where column1 is distinct from lagcolumn1
order by timestamp


column1
timestamp


a
1

b
4

a
6

c
9



SELECT 4