GET latest result from second table in SQL on JOIN

Question

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';

Answer 1

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;