Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015
    Posts
    2

    Unanswered: Sorting an aggregated left join subquery

    Hi all,

    I'm trying to figure out how to display recent topics on a forum along with the actual boards themselves

    I have this SQL:
    Code:
        SELECT 
              F.`board_id`, F.`board`, F.`desc`, C.`cat`, T.`topic`, T.`aname`, 
              T.`author`, T.`tname`, T.`tdate` 
        FROM `f` F 
        LEFT JOIN 
             (SELECT 
                    `topic`, `tname`, `tboard`, `author`, `tdate`, 
                     U.`name` AS `aname` 
              FROM `topics` 
              LEFT JOIN `people` U 
                ON U.`id` = `author` 
              GROUP BY `topic_board`
              ORDER BY `tupdated` DESC) T 
          ON T.`tboard` = F.`board_id` 
        LEFT JOIN `f_cats` C 
          ON C.`cid` = F.`bcat` 
        WHERE F.`plevel` <= ? 
        AND F.`age` <= ?
        ORDER BY C.`cid` ASC
    Which works fantastic EXCEPT for the issue that the subquery here:
    Code:
             (SELECT 
                    `topic`, `tname`, `tboard`, `author`, `tdate`, 
                     U.`name` AS `aname` 
              FROM `topics` 
              LEFT JOIN `people` U 
                ON U.`id` = `author` 
              WHERE `tboard` = F.`board`
              GROUP BY `topic_board`
              ORDER BY `tupdated` DESC) T
    doesn't sort the topics as indicated on the

    Code:
               ORDER BY `tupdated` DESC
    clause... I read around and saw some were saying the order by is ignored on a subquery like this. So are there any other viable alternatives I can use?

    Thanks in advance for all help!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The ordering is only applied to the very outermost query.

    Include tupdated in your subquery SELECT list, then you can use it in the outer query.
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2015
    Posts
    2
    Quote Originally Posted by gvee View Post
    The ordering is only applied to the very outermost query.

    Include tupdated in your subquery SELECT list, then you can use it in the outer query.
    Sorry I think you misunderstood what I'm asking. I made a typo in my original post. It should say topic not topics.

    What you are suggesting I do will reorder the entire query by tupdated which is not what I need

    This is what I'm looking to do:
    Click image for larger version. 

Name:	8PUxQ7O.jpg 
Views:	2 
Size:	17.5 KB 
ID:	16245

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •