postgresql - case when to get the max timestamp between two columns-CodePudding

I want to return the max updated_date timestamp. I have this code:

select 
case when max(t1.updated_date) > max(t2.updated_date)
then t1.updated_date else t2.updated_date
end as MaxDate
from table1 t1
inner join table2 t2
on t1.id = t2.id 
where t1.id = '5'
group by t1.updated_date, t2.updated_date

When I run this code, my result set is both the max updated_date from t1 AND t2:

MaxDate
2021-12-10 8:00:00
2021-12-20 23:00:00

Why is it returning both? How do I get it to return only 2021-12-20 23:00:00 (i.e the max timestamp when comparing the two columns)?

CodePudding user response：

I think you just want a limit query here. As Postgres supports a scalar GREATEST function, you don't need the bulky CASE expression. Consider this version:

SELECT GREATEST(t1.updated_date, t2.updated_date) AS max_updated_date
FROM table1 t1
INNER JOIN table2 t2
    ON t1.id = t2.id 
WHERE t1.id = '5'
ORDER BY 1 DESC
LIMIT 1;