I am trying to left join two tables and return the rows from t1 and matching rows in t2 but only those that have a maximum date.
As an example, I have these two tables:
Code: Select all
dbadmin=> select * from t1;
c1
-------
data2
data1
(2 rows)
Code: Select all
dbadmin=> select * from t2;
c1 | c2 | c3
-------+----+------------
data1 | 1 | 2013-12-19
data1 | 2 | 2013-12-20
(2 rows)
Code: Select all
dbadmin=> select * from t1 left join t2 on t2.c1 = t1.c1;
c1 | c1 | c2
-------+-------+------------
data2 | |
data1 | data1 | 2013-12-19
data1 | data1 | 2013-12-19
(3 rows)
Code: Select all
c1 | c2 | c3
-------+----+------------
data1 | 2 | 2013-12-20
data2 | |
Code: Select all
dbadmin=> select t1.c1, t2.c2, t2.c3 from t1 left join t2 on t2.c1 = t1.c1 and t2.c3 = (select max(t3.c3) from t2 t3 where t3.c1 = t2.c1) order by t1.c1;
ERROR 4816: Subqueries in the ON clause are not supported
Code: Select all
dbadmin=> select t1.c1, t2.c2, t2.c3 from t1 left join t2 on t2.c1 = t1.c1 where t2.c3 = (select max(t3.c3) from t2 t3 where t3.c1 = t2.c1) order by t1.c1;
c1 | c2 | c3
-------+----+------------
data1 | 2 | 2013-12-20
(1 row)