Search code examples
mysqlsqlsql-order-bywindow-functionssql-max

How do I keep the order of my inner join in SQL?

I have the following command:

SELECT * FROM Posts P 
INNER JOIN (SELECT DISTINCT ThreadId FROM Posts ORDER BY Time DESC) R 
ON P.Id = R.ThreadId;

This command selects threads who contain the newest replies. Unfortunately the order of the threads seems to be random. I want the threads to be ordered by the newest replies. In other words: I want my selection to keep the order which I used inside my inner join.

How can I achieve this?

Solution

  • For MySql 8.0+ you can use MAX() window function in the ORDER BY clause:

    SELECT * 
FROM Posts
ORDER BY MAX(Time) OVER (PARTITION BY ThreadId)

    For prior versions use a correlated subquery:

    SELECT p1.* 
FROM Posts p1
ORDER BY (SELECT MAX(p2.Time) FROM Posts p2 WHERE p2.ThreadId = p1.ThreadId)

    You may also want to add as a 2nd argument in the ORDER BY clause , Time DESC.