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

CN <cnliou9(at)fastmail(dot)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