SQL For a given name, get the latest row in a table if its newer than the latest date for that name-CodePudding

If I have two tables table A and table B, for a given name, how can I get the latest for table A only if it's newer than the latest date in table B or the name does not exist in table B.

Attempted but not getting any results when I expect at least the rows from table_a

SELECT t1.* FROM table_a t1 
WHERE t1.date > (SELECT MAX(t2.date) 
FROM table_b t2 
WHERE t1.name = t2.name) 
ORDER BY t1.date DESC LIMIT 1

Table A

id	name	date	state	age
1	John	2022-11-25 05:02:55	NY	32
2	Mary	2022-11-28 08:05:55	HI	26
3	Mary	2022-11-25 01:02:54	FL	25
4	Bill	2022-11-28 05:02:35	NY	32
5	Bill	2022-11-15 05:02:55	HI	26
6	Bill	2022-11-11 07:33:21	FL	25

Table B

id	name	date	college	weight
1	John	2022-11-26 05:02:55	NYU	180
2	Mary	2022-11-27 05:02:55	HIU	140
3	Mary	2022-11-25 05:02:55	FLU	155

Expected Results

id	name	date	state	age
2	Mary	2022-11-28 08:05:55	HI	26
4	Bill	2022-11-28 05:02:35	NY	32

CodePudding user response：

SELECT distinct on (name) * FROM table_a AS a
WHERE NOT EXISTS (
    SELECT true FROM table_b AS b 
    WHERE a.name=b.name AND a.date<b.date )
ORDER BY name, date desc;

latest for table A - distinct on lets you pick the latest record for a given group, ordering by date.
only if it's newer than the latest date in table B or the name does not exist in table B - this translates directly into a not exists subquery expression. I assume you mean the latest date for the same name.

Online demo

CodePudding user response：

try this

SELECT DISTINCT ON a.name
     , a.*
  FROM table_A AS a
  LEFT JOIN LATERAL 
     ( SELECT max(b.date) AS date_max
         FROM table_B AS b
        WHERE b.name = a.name
     ) AS m
    ON True
 WHERE m.date_max IS NULL
    OR a.date >= m.date_max
 ORDER BY a.name, a.date DESC

CodePudding user response：

If I understand correct, this should do what you want:

SELECT 
a.id, a.name, a.date, a.state, a.age
FROM table_a a
WHERE 
(a.name, a.date) IN 
(SELECT a.name, MAX(a.date) FROM table_a a
GROUP BY a.name)
AND ((NOT EXISTS (SELECT 1 FROM table_b b WHERE a.name = b.name))
OR a.date > (SELECT MAX(date) FROM table_b b WHERE a.name = b.name));

The main part of the query, before the AND will fetch every name having the latest date. You can remove the rest of the query to prove that.

Then the other two conditions will be applied. The NOT EXISTS option will find those names which don't appear in the other table.

The second option is the name appears there, but the latest date there is still earlier than in the first table.

Try out here with your sample data: db<>fiddle