Postgres Pro
Downloads
Home   >   mailing lists

Re: Window function? - Mailing list pgsql-general

From John W Higgins
Subject Re: Window function?
Date
Msg-id CAPhAwGzUuk8d9mygJkP=s2pKgjfWURShjXJetEKK21X=Fdm-HQ@mail.gmail.com
Whole thread Raw
In response to Window function?  (Robert Stanford <rstanford@gmail.com>)
List pgsql-general
Tree view


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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Call pstrdup() of palloc.h will change source string, please help!
Next
From: Andreas Joseph Krogh
Date:
Subject: Logical replication of large objects