skip to Main Content

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


  1. 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
    
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
  3. 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;
    
    1. latest for table Adistinct on lets you pick the latest record for a given group, ordering by date.
    2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search