CODEWITHSUNDEEP
postgresqlgreatest-n-per-group
Parwinder Kumar
Parwinder Kumar

Reputation: 81

GET latest result from second table in SQL on JOIN

table_a 

ID        Status      NAME
---------------------------------    
1        pending      RAM
2        pending      SHYAM
3        pending      JULIEN
4        pending      KRISH 
5        pending      Jenkins
6        accepted     K8s

table_b

ID       Values        Datetime     TYPE
---------------------------------------------    
1        L1          2018-06-02      L
1        L2          2019-07-20      L
1        G1          2019-09-20      G
2        L1          2019-09-20      L
2        K1          2019-09-15      K
3        M1          2019-10-22      M
4        R1          2019-10-23      R

Expected result:

NAME     values
--------------   
RAM       L2
SHYAM     L1
JULIEN    ""
KRISH     ""
Jenkins   ""
K8s       ""

Query: I tried below query but it didn't work for me.

SELECT a.NAME,b.values 
FROM table_a a 
   LEFT JOIN table_b b ON a.ID=b.ID 
where a.Status ='pending' 
  and b.type='L';

Upvotes: 2

Views: 58

Answers (2)

GMB
GMB

Reputation: 222402

You can filter with a correlated subquery:

select a.name, b.values
from table_a a
inner join table_b b
    on  a.id = b.id
    and b.type='L'
    and b.datetime = (
        select max(b1.datetime)
        from table_b b1
        where b1.id = b.id
    )
where a.status ='pending'

Upvotes: 0

Jeremy
Jeremy

Reputation: 6713

There are two issues:

  1. In order to get all of the rows from table_a, you will need to move your where conditions to the join clause.
  2. You can use DISTINCT ON in postgres to get the latest entry for each user:
select DISTINCT ON (name)
a.name, b.values
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id AND b.type = 'L' and a.status = 'pending'
ORDER BY name, datetime desc;

Upvotes: 1

Related Questions