On Sat, Jun 4, 2022 at 1:18 AM Robert Stanford <rstanf...@gmail.com> wrote:
> Hi, > > From this: > Input Start End > 5 2022-06-04 09:09:00 2022-06-04 09:09:29 > 4 2022-06-04 09:08:50 2022-06-04 09:09:00 > 4 2022-06-04 09:08:10 2022-06-04 09:08:50 > 4 2022-06-04 09:07:47 2022-06-04 09:08:10 > 17 2022-06-04 09:06:47 2022-06-04 09:07:47 > 4 2022-06-04 09:06:37 2022-06-04 09:06:47 > 4 2022-06-04 09:06:29 2022-06-04 09:06:37 > 4 2022-06-04 09:06:17 2022-06-04 09:06:29 > 4 2022-06-04 09:05:53 2022-06-04 09:06:17 > 16 2022-06-04 09:04:33 2022-06-04 09:05:53 > > To this: > Input Start End > 5 2022-06-04 09:09:00 2022-06-04 09:09:29 > 4 2022-06-04 09:07:47 2022-06-04 09:09:00 > 17 2022-06-04 09:06:47 2022-06-04 09:07:47 > 4 2022-06-04 09:05:53 2022-06-04 09:06:47 > 16 2022-06-04 09:04:33 2022-06-04 09:05:53 > lag is indeed your friend here - assuming times is your table name with times_cte as (select *, lag(input, 1) over () from times) select input, start, end from times_cte where input != coalesce(lag, -1); The coalesce to -1 is needed at the end to get the first row which has null for the lag value because it's the first row. John
