Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Oct 2009
    Posts
    410
    Thanks
    6
    Thanked 3 Times in 3 Posts

    Can I GROUP BY results after ORDER BY

    I can not seem to get the first query to return the results in 'dateAdded' order. I am after the most recent result in each group to be returned.

    It seems that the grouping is done in a different way than I expected it to be done in.

    I guess what I am after is the same as my second query, but them to have them grouped after the ORDER BY `c`.`dateAdded` DESC not before, which is what I think is happening?

    Code:
    SELECT	`c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`,
    `i`.`userid` AS `itemOwner`, `i`.`title`,
    `c`.`userid` AS `posterId`, `poster`.`fullname` AS `posterName`
    FROM `comments` as `c`
    LEFT JOIN `items` as `i` ON `i`.`itemid` = `c`.`itemid`
    LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
    WHERE `c`.`userid` = '" . mysql_real_escape_string($_SESSION['user']['userid']) . "'
    GROUP BY `c`.`itemid`
    ORDER BY `c`.`dateAdded` DESC




    The second one returns the results as expected, in dateAdded order.
    Code:
    SELECT	`c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`,
    `i`.`userid` AS `itemOwner`, `i`.`title`,
    `c`.`userid` AS `posterId`, `poster`.`fullname` AS `posterName`
    FROM `comments` as `c`
    LEFT JOIN `items` as `i` ON `i`.`itemid` = `c`.`itemid`
    LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
    WHERE `c`.`itemid` = '" . $getCommentTitle['itemid'] . "'
    ORDER BY `c`.`dateAdded` DESC

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Your big problem is that MySQL allows sloppiness in GROUP BY that other databases, fortunately, do not allow.

    Because of this sloppiness, some MySQL results are indeed not what you wanted.

    A *proper* use of GROUP BY requires that EVERY field in the SELECT except those that are aggregate function call be listed. (Aggregate functions are ones that work across several records, such as COUNT(), MAX(), MIN(), SUM(), AVG(), etc.)

    So in your query, a *PROPER* GROUP BY would have to include EVERY field in your SELECT! (Since you have no aggregate functions.)

    Since you didn't bother to tell us *HOW* the data comes out wrongly ordered, though, I can't really know what the answer to your question is.

    I *suspect* the answer lies in not using GROUP BY at all and, instead, adding more terms to your ORDER BY. But that's a guess.

    If you would care to show us an example of the ordering you are now getting and then an example of the ordering you want, we could probably help you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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