Page 1 of 1

[MySQL] DISTINCT / JOIN ordering issue

Posted: Tue Mar 31, 2009 12:45 am
by aschlosberg
I am creating a forum with the following (simplified) table structure:

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
 
My (incorrect) thought was that the DISTINCT clause would only be applied after the ORDERing thus returning threads ordered by forum, then "stickiness" and finally most recently posted in.

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.

Re: [MySQL] DISTINCT / JOIN ordering issue

Posted: Thu Apr 02, 2009 6:22 am
by max529
Try this one,

Code: Select all

 
SELECT *  from  `posts` AS `p1`
                           left join `posts` AS `p2`
                              ON  (`p1`.`time` < `p2`.`time`  && `p1`.`thread_id`=`p2`.`thread_id`)  
                                      join `threads` 
                                         AS `t` on `p1`.`thread_id`=`t`.`id` 
                                               where `p2`.`time` is null order by `p1`.`time`

Regards,
Max