CODEWITHSUNDEEP
phpsql
goartur
goartur

Reputation: 13

How can I order topics by post.date_written?

I have 2 tables(I'm making kind of forum) I would like to sort topics order by last post that has been written(order by row 'posts.date_written'). I was trying to do it by myself but I couldn't get it working.

Topic table:

ID|id_subforum|owner_id|owner|title|description

Posts table:

ID|id_subforum|id_topic|by|description|date_written

Can somebody show me how should this SQL look like?

Upvotes: 0

Views: 16

Answers (1)

Barmar
Barmar

Reputation: 781721

SELECT t.*
FROM topics AS t
LEFT JOIN (
    SELECT id_topic, id_subforum, MAX(date_written) AS last_date
    FROM posts
    GROUP BY id_topic, id_subforum
) AS p
ON t.id = p.id_topic AND t.id_subforum = p.id_subforum
WHERE t.id_subforum = '$forumId'
ORDER BY last_date DESC

Upvotes: 1

Related Questions