#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0

    How to group by one field and order by another?


    I'm trying to pull the most recent posts out of a table, but have each topic represented only once.

    This gets the posts, but takes more than one from each topic:
    SELECT topic_id,posted,poster,message FROM posts ORDER BY posted DESC LIMIT 20
    Adding a GROUP BY makes sure each thread is represented only once, but then I no longer get the most recent post from each topic:
    SELECT topic_id,posted,poster,message FROM posts GROUP BY topic_id ORDER BY posted DESC LIMIT 20
    I tried to put "ORDER BY" before "GROUP BY", but that just gives me a syntax error:
    SELECT topic_id,posted,poster,message FROM posts ORDER BY posted DESC LIMIT 20[ GROUP BY topic_id
    How should I write my query?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    This is a GROUPWISE MAX or TOP-n problem. (and, while frowned upon - by me anyway, a tiny tweak to your existing query (parentheses) will get you a working, if undocumented, solution). But 'better' solutions exist.

    Does that help?
    Last edited by cafelatte; October 4th, 2012 at 01:50 AM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    Huh?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    http://bit.ly/WniEPG
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    Wow, thanks for the condescension. For your information, I'd already found that page, but nothing on it gave any clue as to how I could fix my query by adding only parentheses or why that solution is "frowned upon". But whatever, it seems like you only post here in order to be able to insult those who are puzzled by your ultra-ambiguous "help". You spent more time constructing an insulting URL than it would have taken to actually be helpful. It's clear which is more important to you.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    michael, for you to bitch about one of our most helpful members after having joined only five minutes ago is pretty arrogant

    yes, you might not have appreciated the lmgtfy link, but really, "this is a GROUPWISE MAX problem" should have been enough for you

    and if you're having trouble interpreting that page in the mysql manual and how it applies to your situation, don't take it out on him, okay?

    your solution involves joining your posts table to a subquery which finds the latest post by topic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    Originally Posted by r937
    michael, for you to bitch about one of our most helpful members after having joined only five minutes ago is pretty arrogant
    Oh, so if I don't cheerfully accept someone's insult then I'm "bitchy" and "arrogant"? Right. Well, it's pretty clear what kind of forum this is. If cafelatte is truly one of this forum's most helpful members, he certainly didn't show it in this thread. And I've certainly never had this kind of experience elsewhere.

    and if you're having trouble interpreting that page in the mysql manual and how it applies to your situation, don't take it out on him, okay?
    Did you even read my reply? He made a cryptic reference to something *other* than groupwise-max without explaining it. It's almost as if he were trying to bait me just so he could gleefully insult me with the "you were too stupid to Google it!" link.

    Whatever. I won't seek help here again.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by MichaelBluejay
    Whatever. I won't seek help here again.
    "the greatest danger for a person is to have contempt for the world and what is in it—contempt defined as the false importance or glory from the lessening of things not oneself"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    Originally Posted by r937
    "the greatest danger for a person is to have contempt for the world and what is in it—contempt defined as the false importance or glory from the lessening of things not oneself"
    What, you think you're helping your position by using quotes from a notorious mind-control cult?

    In any event, yep, you and cafelatte are showing your contempt rather blatantly.

IMN logo majestic logo threadwatch logo seochat tools logo