Message-Id: 
 <1471968815.2343352.703756417.6A1C9C13@webmail.messagingengine.com>
From: CN <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Subject: Display group title only at the first record within each group
Date: Wed, 24 Aug 2016 00:13:35 +0800
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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