# Get start of a time period from repeated groups

id – id of place where action happened
t – time of action

``+----+----------+ | id |    t     | +----+----------+ |  1 | 12:10:00 | |  1 | 12:10:05 | |  1 | 12:11:00 | |  1 | 13:04:03 | |  2 | 14:18:05 | |  2 | 15:00:09 | |  3 | 17:33:50 | |  1 | 20:03:14 | |  1 | 20:03:55 | |  1 | 20:10:23 | +----+----------+ ``

Goal is to get this output

``+----+----------+ | id |  start   | +----+----------+ |  1 | 12:10:00 | |  2 | 14:18:05 | |  3 | 17:33:50 | |  1 | 20:03:14 | +----+----------+ ``

start – time of first action at id

Scripts with rank, min, etc. keep grouping rows with id=1
I do not know how to resolve this issue and did not find similar post
Here is sqlfiddle with scripts

This type of problem can be termed as gaps-and-islands problem and can be achieved by the difference of row numbers and aggregation.

``select id,min(t),min(h) from ( select id       ,t       ,extract(hour from t) h       ,row_number() over (order by t) as seq1       ,row_number() over (partition by id order by t) as seq2   from records ) t group by id,(seq1-seq2) order by min(t); ``

Reference: db<>fiddle

It’s a typical gap and islands problem in which you can use some analytic functions such as `ROW_NUMBER()`, `LAG()`, `LEAD()` etc. mostly we consider applying analytic functions twice through manipulating `PARTITION` option, and substracting one result from the other such as

``SELECT DISTINCT tt.ID, FIRST_VALUE(t) OVER W AS start   FROM (SELECT t.*,                ROW_NUMBER() OVER(ORDER BY t)               - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY t) AS rn                   FROM (SELECT ID, t FROM records) t) tt WINDOW W AS           (PARTITION BY rn ORDER BY t ROWS               BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  ORDER BY start;   +----+----------+  | id |  start   |  +----+----------+  |  1 | 12:10:00 |  |  2 | 14:18:05 |  |  3 | 17:33:50 |  |  1 | 20:03:14 |  +----+----------+ ``

Demo

The simplest way to solve this is to use `lag()`:

``select id, t as start from (select t.*, lag(id) over (order by t) as prev_id       from t      ) t where prev_id is distinct from id; ``

Basically, you just want the values when the `id` changes.

Note: I think that looking at this as a "typical" gaps-and-islands problem is overkill and complicates the solution.