[MySQL] DISTINCT / JOIN ordering issue

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
aschlosberg
Forum Newbie
Posts: 24
Joined: Fri Jan 23, 2009 10:17 pm

[MySQL] DISTINCT / JOIN ordering issue

Post 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.
max529
Forum Commoner
Posts: 50
Joined: Sat May 19, 2007 4:10 am

Re: [MySQL] DISTINCT / JOIN ordering issue

Post 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
Post Reply