0

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?

  • 1
    The JOIN condition looks weird. books.user_id = #{self.id} OR posts.user_id = #{self.id}? Perhaps you wanted AND there instead of OR? – ypercubeᵀᴹ Nov 16 '18 at 13:16
  • Keep in mind that GREATEST and LEAST ignore NULL values. Have a look at Postgresql docs about it. – McNets Nov 16 '18 at 14:38
  • Perhaps it's my lack of Postgres knowledge but ... I don't see a join clause between the two tables ... do you really want a cartesian product? – markp Nov 16 '18 at 14:52
  • @McNets Thank you, I appreciate that. It's no problem if they ignore all NULL values, I have that built into my code. I appreciate the heads up. – JapanRob Nov 17 '18 at 2:06
  • @ypercubeᵀᴹ Thank you, I appreciate it. I'm not sure that's where the issue is coming from, but let me take a look and get back to you if it is. – JapanRob Nov 17 '18 at 2:07
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.