CODEWITHSUNDEEP
sqlpostgresqlmaxcross-join
xindi
xindi

Reputation: 27

PostgreSQL cross join using max returns null

I want to select the max() value of the modified_at column in each of several tables:

select 
max(a.modified_at) as a_modified_at,
max(b.modified_at) as b_modified_at,  
max(c.modified_at) as c_modified_at 
from a, b, c;

This works correctly as long as each of the tables has at least 1 row. The problem is that when just one of the tables has 0 rows, null is returned for all tables:

null,null,null

What is a solution that returns the correct values for the tables that do have rows? PostgreSQL-10

Upvotes: 0

Views: 137

Answers (1)

Bergi
Bergi

Reputation: 664365

Using OUTER JOINs should do it

select 
max(a.modified_at) as a_modified_at,
max(b.modified_at) as b_modified_at,  
max(c.modified_at) as c_modified_at 
from a outer join b outer join c;

but a possibly simpler option would be to use 3 subqueries instead:

select 
(select max(modified_at) from a) as a_modified_at,
(select max(modified_at) from b) as b_modified_at,  
(select max(modified_at) from c) as c_modified_at;

Upvotes: 1

Related Questions