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 |
3
Answers
try this
If I understand correct, this should do what you want:
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
distinct on
lets you pick the latest record for a given group, ordering by date.not exists
subquery expression. I assume you mean the latest date for the same name.Online demo