threads
- id
- forum_id
- sticky (bit signalling if they must stay up the top for those unfamiliar)
posts
- id
- thread_id
- time
My goal is to, in a single query, return all threads ordered by which was posted in most recently. My current (irrelevant parts removed) query is:
Code: Select all
SELECT DISTINCT(`t`.`id`) AS `id`,
`t`.`forum_id` AS `forum_id`
FROM `threads` AS `t`
INNER JOIN `posts` AS `p`
ON `p`.`thread_id` = `t`.`id`
ORDER BY `t`.`forum_id` ASC,
`t`.`sticky` DESC,
`p`.`time` DESC
The ordering by forum and and "stickiness" works fine but the post time is ignored (I can understand why MySQL would implement it this way). If I remove the DISTINCT the ordering is correct but I will then obviously have to remove multiple occurrences of the same thread_id via PHP.
Thanks for any advice that you can offer.