SQL ORDER BY with 2 timestamp columns-CodePudding

I'm trying to build a blog-type website for a few of my programmer friends where we can share tips and articles about code, Linux, software, etc.

I'm building the post system in PostgreSQL and so far it's been going quite good. The thing that stumps me however is the sorting of two timestamptz columns with ORDER BY. I want to be able to have a created timestamp, but also a modified timestamp. This should sort by newest post (created OR modified most recently). I came up with this -- post 135 should be on top but the modified posts are taking precedence.

I would preferably like to have both modified and created fields available so I can display: "created on xx-xx-xx, last updated yy-yy-yy".

SELECT posts.postid, users.id, posts.modified, posts.created
FROM posts
JOIN users ON posts.userid=users.id
WHERE posts.isdraft=false
ORDER BY posts.modified DESC NULLS LAST, posts.created DESC;

 postid | id  |           modified            |            created
-------- ----- ------------------------------- -------------------------------
    100 | 999 | 2022-11-28 01:57:07.495482-06 | 2022-11-27 21:43:34.132985-06
    115 | 111 | 2022-11-28 01:55:05.9358-06   | 2022-11-27 21:43:34.137873-06
    135 | 999 |                               | 2022-11-28 02:28:20.64009-06
    130 | 444 |                               | 2022-11-28 01:42:49.301489-06
    110 |  42 |                               | 2022-11-27 21:43:34.137254-06

(the reason for the JOIN is that I'll need the username attached to the user id but I omitted it here for space)

All help is appreciated, thanks!

CodePudding user response：

Sort by greatest of the two timestamps. Here is your query with this modification.

SELECT posts.postid, users.id, posts.modified, posts.created
FROM posts
JOIN users ON posts.userid=users.id
WHERE not posts.isdraft
ORDER BY greatest(posts.modified, posts.created) DESC;