How do I sort two selections by date?

Question

I have the following command:

SELECT * 
FROM Posts P 
WHERE P.ThreadId = 0
ORDER BY 
    (SELECT MAX(R.Time) 
     FROM Posts R 
     WHERE R.ThreadId = P.Id) DESC

This selects all threads and orders them by the time of their last reply. Threads without a reply are always behind threads with replies and in random order. I want this command to also order threads without replies by their creation time intermingled with the other threads.

How can I achieve this? (Side note: Threads and Replies are using the same table "Post". A thread has the ThreadId 0 while a reply has the ThreadId of a parent post.)

user_3776927 · Answer

When there is no element in the subquery fullfilling the condition (ie there is not reply to it) the subquery will return NULL thus you have to provide a default value. The function for that is COALESCE, which returns the first non-null value of its arguments.

SELECT * 
FROM Posts P 
WHERE P.ThreadId = 0
ORDER BY 
    COALESCE((SELECT MAX(R.Time) 
     FROM Posts R 
     WHERE R.ThreadId = P.Id), P.Time) DESC

So Coalesce( _thesubquery_, P.Time) will return the result of the subquery, if it's not null, or P.Time (ie the creation time of the non-answered post) otherwise.

You can also apply the COALESCE function just around the max instead of the whole subquery. This will lead to the same result

SELECT * 
FROM Posts P 
WHERE P.ThreadId = 0
ORDER BY 
    (SELECT COALESCE(MAX(R.Time), P.Time)
     FROM Posts R 
     WHERE R.ThreadId = P.Id) DESC

user_10138734 · Answer

SELECT p1.*,
       COALESCE(p3.time, p1.time) last_thread_time
FROM posts p1
LEFT JOIN LATERAL ( SELECT MAX(p2.time) time
                    FROM posts p2
                    WHERE p2.thread_id = p1.id ) p3 ON TRUE
WHERE p1.thread_id = 0
ORDER BY COALESCE(p3.time, p1.time);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fc1a21abf4b4987a591c5e0536369142

How do I sort two selections by date?

2 Answers