Left Join table with first match historically What I want to accomplish is to join TableB to TableA where TableA.date &lt;= 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:

postgres version: PostgreSQL 9.2.24
Scripts:
CREATE TABLE public.table_a (
    id serial8 NOT NULL,
    value text NULL,
    &quot;date&quot; timestamp NOT NULL DEFAULT now()
);

INSERT INTO public.table_a
(value, &quot;date&quot;)
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,
    &quot;date&quot; timestamp NOT NULL DEFAULT now()
);

INSERT INTO public.table_b
(value, &quot;date&quot;)
VALUES
('Task A', '2020-02-05'::date),
('Task B', '2020-02-15'::date);
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.&quot;date&quot; DESC) rn 
              FROM table_a
              JOIN table_b ON table_a.&quot;date&quot; &lt;= table_b.&quot;date&quot;)
SELECT value_b, value_a
FROM cte
WHERE rn = 1;

fiddle
Check does this is applicable to your server version.