Re: Window function?

On Sat, Jun 4, 2022 at 1:18 AM Robert Stanford <rstanford(at)gmail(dot)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