agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: CN <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
Subject: Display group title only at the first record within each group
Date: Wed, 24 Aug 2016 00:13:35 +0800
Message-ID: <1471968815.2343352.703756417.6A1C9C13@webmail.messagingengine.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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

view thread (2+ messages)  latest in thread

Message-ID: <1471968815.2343352.703756417.6A1C9C13@webmail.messagingengine.com>
Permalink:  ../1471968815.2343352.703756417.6A1C9C13@webmail.messagingengine.com/
Also on:    postgresql.org/message-id/1471968815.2343352.703756417.6A1C9C13@webmail.messagingengine.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: cnliou9@fastmail.fm
  Subject: Re: Display group title only at the first record within each group
  In-Reply-To: <1471968815.2343352.703756417.6A1C9C13@webmail.messagingengine.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox