How to take max between two values in presto?

user_12124476 0
Viewed 9399

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

presto sql
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.

user_11440350

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)
user_135658890
Related