How to get last value with condition in postgreSQL?

Question

I have a table in postgres with three columns, one with a group, one with a date and the last with a value.

grp	mydate	value
A	2021-01-27	5
A	2021-01-23	10
A	2021-01-15	15
B	2021-01-26	7
B	2021-01-24	12
B	2021-01-15	17

I would like to create a view with a sequence of dates and the most recent value on table for each date according with group.

grp	mydate	value
A	2021-01-27	5
A	2021-01-26	10
A	2021-01-25	10
A	2021-01-24	10
A	2021-01-23	10
A	2021-01-22	15
A	2021-01-21	15
A	2021-01-20	15
A	2021-01-19	15
A	2021-01-18	15
A	2021-01-17	15
A	2021-01-16	15
A	2021-01-15	15
B	2021-01-27	7
B	2021-01-26	7
B	2021-01-25	12
B	2021-01-24	12
B	2021-01-23	17
B	2021-01-22	17
B	2021-01-21	17
B	2021-01-20	17
B	2021-01-19	17
B	2021-01-18	17
B	2021-01-17	17
B	2021-01-16	17
B	2021-01-15	17

SQL code to generate the table:

CREATE TABLE foo (
    grp char(1),
    mydate date,
    value integer);

INSERT INTO foo VALUES
('A', '2021-01-27', 5),
('A', '2021-01-23', 10),
('A', '2021-01-15', 15),
('B', '2021-01-26', 7),
('B', '2021-01-24', 12),
('B', '2021-01-15', 17)

I have so far managed to generate a visualization with the sequence of dates joined with the distinct groups, but I am failing to get the most recent value.

SELECT DISTINCT(foo.grp), (date_trunc('day'::text, dd.dd))::date AS mydate
   FROM foo, generate_series((( SELECT min(foo.mydate) AS min
           FROM foo))::timestamp without time zone, (now())::timestamp without time zone, '1 day'::interval) dd(dd)

Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Bergi, Jan 29 '21 at 01:43
I guess, your expected output is wrong: In the B group you wanted to have the value "12" from day 24 down to day 16 if you would follow the pattern used in the rest. — S-Man, Jan 29 '21 at 07:23
@S-Man it was wrong for group A. Made the corrections, thanks. — Rafael Leite, Jan 29 '21 at 10:42
@Bergi this question is a bit different, because it is seaching for a global first or last. I am searching for the last register before determinate date. I tried to use some os these solutions, but with no sucess. — Rafael Leite, Jan 29 '21 at 10:45

S-Man · Accepted Answer · 2021-01-29T10:52:12.263

step-by-step demo:db<>fiddle

SELECT
    grp,
    gs::date as mydate,
    value
FROM (
    SELECT
        *,
        COALESCE(                                                             -- 2
             lead(mydate) OVER (PARTITION BY grp ORDER BY mydate) - 1,        -- 1 
             mydate
        ) as prev_date
    FROM foo
) s,
    generate_series(mydate, prev_date, interval '-1 day') as gs               -- 3
ORDER BY grp, mydate DESC                                                     -- 4

lead() window function shifts the next value of an ordered group (= partition) into the current one. The group is already defined, the order is the date. This can be used to create the required date range. Since you don't want to have the last date twice (as end of the first range and beginning of the next one) the end date stops - 1 (one day before the next group starts)
This is for the very last records of the groups: They don't have a following record, so lead() yield NULL. To avoid this, COALESCE() sets them to the current record.
Now, you can create a date range with the current and the next date value using generate_series().
Finally you can generate the required order

Worked fine! The only change I had to made was using "NOW()" as second argument of COALESCE function, because I don't wanted the list to stop in the date of last register, but on today date. — Rafael Leite, Feb 09 '21 at 10:02

How to get last value with condition in postgreSQL?

1 Answers1