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:
SELECTCASE 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 ,amountFROM x
) t
Would someone please give me a hand?
Best Regards,
CN
--
http://www.fastmail.com - IMAP accessible web-mail
