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

Question

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?

Have you tried switching it, so threadids are done in the "from" segment, and then join the posts onto that? — Monofuse, Aug 12 '21 at 12:57

score 1 · Answer 1 · answered Aug 12 '21 at 13:07

Your join needs to group and select the last post per thread. The order needs to go on the outside query (not the subquery).

SELECT *
FROM Threads AS t 
LEFT JOIN (
    SELECT ThreadId, MAX(Time) AS LastPost
    FROM Posts
    GROUP BY ThreadId
    ) AS r ON r.ThreadId = t.ThreadId
ORDER BY LastPost DESC

You can use INNER JOIN instead of LEFT JOIN if you want to exclude threads that have no posts (if that is even possible).

score 1 · Accepted Answer · answered Aug 12 '21 at 13:14

1

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.

answered Aug 12 '21 at 13:14

forpas

160,666
10
38
76

This solved my problem and seems very elegant. Thank you. – moccajoghurt Aug 12 '21 at 13:27
Bonus points for not adopting my "inner join" approach, but finding the most effective solution. – moccajoghurt Aug 12 '21 at 13:35

score 0 · Answer 3 · answered Aug 12 '21 at 13:00

0

you could change the order of your query like this

    SELECT  ...
    FROM    BrandsProducts
    INNER JOIN Brands ON BrandsProducts.brandid = BrandsProducts.brandid
    WHERE   ...
    ORDER BY ...

answered Aug 12 '21 at 13:00

lvor

11
3

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

3 Answers3