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