I have the following query:
select id, table1.date1, table2.date2, table1.name from table1 join table2 using (id) I want also to have another column with MAX(table1.date1, table2.date2) but I don't find the proper syntax for that. I don't want MAX to go over all rows in table and take MAX() I want it to select max from the two values specified in the row.
Example:
id date1 date2 name max 1 2020-01-01 2020-04-01 A 2020-04-01 2 2019-02-01 2020-01-03 B 2020-01-03 3 2019-02-01 null c 2019-02-01 I can't also do group by because I don't want to group anything here. It's more similar to coalesce give a function list of values and choose the max value from it
2 Answers
Use greatest():
select id, t1.date1, t2.date2, t1.name, greatest(t1.date1, t2.date2) from table1 t1 join table2 t2 using (id); Note that greatest() returns NULL if any argument is NULL. So, if you have NULL values, you will need special care.
you can try it
select id, table1.date1, table2.date2, table1.name, case when table1.date1 > table1.date2 then table1.date1 else table1.date2 end as max_date from table1 join table2 using (id) 
