agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedDisplay group title only at the first record within each group
2+ messages / 2 participants
[nested] [flat]
* Display group title only at the first record within each group
@ 2016-08-23 16:13 CN <cnliou9@fastmail.fm>
0 siblings, 1 reply; 2+ messages in thread
From: CN @ 2016-08-23 16:13 UTC (permalink / raw)
To: pgsql-sql
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
Note that "dt" is sorted as if clause
ORDER BY name,dt
was applied to SELECT.
With this SELECT:
SELECT name
,ROW_NUMBER() OVER (PARTITION BY name) AS rn_name
,dt
,ROW_NUMBER() OVER (PARTITION BY name,dt) AS rn_dt
,amount
FROM x;
I get this result:
john 2 2016-07-08 1 30
john 4 2016-07-08 2 50
john 3 2016-08-19 1 40
john 1 2016-08-20 1 80
mary 1 2016-08-17 1 20
mary 2 2016-08-17 2 30
Above result shows that records are not sorted by rn_name and rn_dt.
Were the above records correctly sorted "BY rn_name,rn_dt", the
following SELECT probably would fulfill my ultimate goal:
SELECT
CASE WHEN rn_name=1 THEN name ELSE NULL END
,CASE WHEN rn_dt=1 THEN dt ELSE NULL END
,amount
FROM (
SELECT name
,ROW_NUMBER() OVER (PARTITION BY name) AS rn_name
,dt
,ROW_NUMBER() OVER (PARTITION BY name,dt) AS rn_dt
,amount
FROM x
) t
Would someone please give me a hand?
Best Regards,
CN
--
http://www.fastmail.com - IMAP accessible web-mail
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Display group title only at the first record within each group
@ 2016-08-24 08:03 Harald Fuchs <hari.fuchs@gmail.com>
parent: CN <cnliou9@fastmail.fm>
0 siblings, 0 replies; 2+ messages in thread
From: Harald Fuchs @ 2016-08-24 08:03 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2016-08-24 08:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-08-23 16:13 Display group title only at the first record within each group CN <cnliou9@fastmail.fm>
2016-08-24 08:03 ` Harald Fuchs <hari.fuchs@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox