CODEWITHSUNDEEP
phpmysqlsqlsorting
Neku
Neku

Reputation: 85

Sort replies and new thread by date

I want my board to sort threads in the order that new threads are displayed at the top until another new thread is created or a reply has been made in another thread. If this happens the thread will go down from 1 to 2 and so on.

However the code I'm using right now only does this: if a new thread is created it will be displayed at the bottom, but if you make a reply it will go to the top. So all that's being needed to fix is making it showing the newest thread created on the top aswell.

    SELECT b.id, b.name, b.subject, b.maintext, b.ip, b.date, b.img, b.replycount, MAX(r.date)
FROM 
    board b
    LEFT JOIN reply r ON r.id = b.id

GROUP BY b.id, b.date
ORDER BY GREATEST(b.date, MAX(r.date)) DESC
LIMIT 50;

Upvotes: 1

Views: 110

Answers (1)

Aprillion
Aprillion

Reputation: 22330

greatest will return null if any of the arguments are null => i suggest using coalesce, if you care about new threads and new replies, but not about updates to the original thread:

order by coalesce(MAX(r.date), b.date)

if you care about the updates too (so b.date can be greater than max(r.date)):

order by greatest(b.date, coalesce(MAX(r.date), b.date))

Upvotes: 2

Related Questions