0

Table A

Table B

enter image description here

What I want to accomplish is to join TableB to TableA where TableA.date <= TableB.date, but only the the value with the nearest max date to Table.B.date. Currently, what I get is all the previous value like this:

![enter image description here

postgres version: PostgreSQL 9.2.24

Scripts:

CREATE TABLE public.table_a (
    id serial8 NOT NULL,
    value text NULL,
    "date" timestamp NOT NULL DEFAULT now()
);

INSERT INTO public.table_a
(value, "date")
VALUES
('Value 1', '2020-01-29'::date),
('Value 2', '2020-02-01'::date),
('Value 3', '2020-02-12'::date);


CREATE TABLE public.table_b (
    id serial8 NOT NULL,
    value text NULL,
    "date" timestamp NOT NULL DEFAULT now()
);

INSERT INTO public.table_b
(value, "date")
VALUES
('Task A', '2020-02-05'::date),
('Task B', '2020-02-15'::date);
0
WITH cte AS ( SELECT table_a.value value_a, 
                     table_b.value value_b, 
                     ROW_NUMBER() OVER (PARTITION BY table_b.value ORDER BY table_a."date" DESC) rn 
              FROM table_a
              JOIN table_b ON table_a."date" <= table_b."date")
SELECT value_b, value_a
FROM cte
WHERE rn = 1;

fiddle

Check does this is applicable to your server version.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.