Thread: Display group title only at the first record within each group

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

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