[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] Display group title only at the first record within each group
From: Harald Fuchs <hari.fuchs () gmail ! com>
Date: 2016-08-24 8:03:27
Message-ID: 87d1ky1tm8.fsf () hf ! protecting ! net
[Download RAW message or body]
CN <cnliou9@fastmail.fm> writes:
> Hi!
>
> Such layout is commonly seen on real world reports where duplicated
> group titles are discarded except for the first one.
>
> CREATE TABLE x(name TEXT,dt DATE,amount INTEGER);
>
> COPY x FROM stdin;
> john 2016-8-20 80
> mary 2016-8-17 20
> john 2016-7-8 30
> john 2016-8-19 40
> mary 2016-8-17 30
> john 2016-7-8 50
> \.
>
> My desired result follows:
>
> john 2016-07-08 50
> 30
> 2016-08-19 40
> 2016-08-20 80
> mary 2016-08-17 20
> 30
Use window functions:
SELECT CASE
WHEN lag(name) OVER (PARTITION BY name ORDER BY name, dt) IS NULL
THEN name
ELSE NULL
END,
CASE
WHEN lag(dt) OVER (PARTITION BY name, dt ORDER BY name, dt) IS NULL
THEN dt
ELSE NULL
END,
amount
FROM x
ORDER BY name, dt
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic