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?



Answers 1


So, I got it figured out and I'm leaving the answer for posterity.

Essentially, remove the * from the query and add a LIMIT 1 and it all appears to work. In addition, as @ypercubetm stated, you can use AND in place of OR, but it depends on your usecase.

SELECT GREATEST(books.updated_at, books.deleted_at, posts.updated_at, posts.deleted_at) AS greatest_time
FROM books, posts WHERE books.user_id = 1
OR posts.user_id = 1 ORDER BY greatest_time DESC LIMIT 1;

returns a single, latest time.

JapanRob
JapanRob
November 17, 2018 02:37 AM

Related Questions


GROUP BY and ORDER BY problem

Updated March 15, 2019 17:06 PM

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