October 3rd, 2012, 08:19 PM
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:
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:
I tried to put "ORDER BY" before "GROUP BY", but that just gives me a syntax error:
How should I write my query?
October 4th, 2012, 01:46 AM
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.
October 4th, 2012, 02:12 AM
October 4th, 2012, 02:42 AM
October 4th, 2012, 11:49 AM
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.
October 4th, 2012, 12:45 PM
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
October 4th, 2012, 12:59 PM
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.
Originally Posted by r937
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.
October 4th, 2012, 03:20 PM
"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"
Originally Posted by MichaelBluejay
October 4th, 2012, 06:01 PM
What, you think you're helping your position by using quotes from a notorious mind-control cult?
Originally Posted by r937
In any event, yep, you and cafelatte are showing your contempt rather blatantly.