| Welcome to WebmasterWorld Guest from 188.8.131.52 |
register, login, search, subscribe, help, library, PubCon, announcements, recent posts, open posts,
|Subscribe to WebmasterWorld|
|GROUP BY, returning the highest something per group?|
| 3:39 pm on Mar 8, 2011 (gmt 0)|
maybe I would solve this easier once I've had more coffee, but right now I'm not getting it. help!
number | letter | word
1 | a | one
2 | a | two
3 | a | three
4 | b | four
5 | b | five
SELECT letter, number, word FROM table GROUP BY letter
what it returns is:
a 1 one
b 4 four
What I need is to get the maximum, or highest number in each letter group. Not the lowest.
a 3 three
b 5 five
ORDER BY is merely ordering the results after grouping.
Is there a way to do this in one simple query?
| 3:44 pm on Mar 8, 2011 (gmt 0)|
I know I can do SELECT MAX(number)
but that doesn't return the word column
I'd never noticed this:
SELECT MAX(number),word FROM table GROUP BY letter
isn't that weird.
| 4:36 pm on Mar 8, 2011 (gmt 0)|
now I've got this:
select * from table where number in (select max(number) from table group by letter)
it works - I've got the rows I want.
This seems kludgy to me. doing one select to get a list of ids, then another "IN" select to get the rows? it works, but isn't there a better way?
| 4:53 pm on Mar 8, 2011 (gmt 0)|
That's pretty much the way I'd do it.
All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
WebmasterWorld ® and PubCon ® are a Registered Trademarks of Pubcon Inc.
© Pubcon Inc. 1996-2012 all rights reserved