Postgres 13

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

create or replace function first_in_series_func(int[], int)
returns int[] language sql immutable
as $$ 
    select case 
        when $1[2] is distinct from $2- 1 then array[$2, $2]
        else array[$1[1], $2] end; 
$$;

create or replace function first_in_series_final(int[])
returns int language sql immutable
as $$
    select $1[1]
$$;

create aggregate first_in_series(int) (
    sfunc = first_in_series_func,
    finalfunc = first_in_series_final,
    stype = int[]
);

CREATE FUNCTION



CREATE FUNCTION



CREATE AGGREGATE


-- table

drop table if exists my_table;
create table my_table(id int primary key);
insert into my_table values
(1),(2),(3),
(6),(7),(8),
(10),
(23),(24),(25);

DROP TABLE



CREATE TABLE



INSERT 0 10


-- query

select *, first_in_series(id) over (order by id)
from my_table


id
first_in_series


1
1

2
1

3
1

6
6

7
6

8
6

10
10

23
23

24
23

25
23



SELECT 10