Postgres Greatest Query not returning correct timestamp

by JapanRob   Last Updated November 16, 2018 14:06 PM

I think I might be writing this query incorrectly. Essentially, I want to find the object with the latest timestamp across four possible values (in this example, user_id is 1).

SELECT *, GREATEST(books.updated_at, books.deleted_at, posts.updated_at, posts.deleted_at)
FROM books, posts WHERE books.user_id = 1
OR posts.user_id = 1

This will always return a time for updated at, but even if the deleted_at time for one of the objects is the latest, it will not return a deleted_at time. I know, because running a query for deleted_at IS NOT NULL returned a later timestamp.

What am I doing wrong here?

Related Questions

Get top 5 on 2 distinct where clause

Updated October 05, 2016 09:02 AM

Selecting the max date out of a text group

Updated April 11, 2018 15:06 PM

PostgreSQL - Minimum number of rows per group

Updated May 02, 2015 21:02 PM