homepage Welcome to WebmasterWorld Guest from 54.211.34.105
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
GROUP BY, returning the highest something per group?
httpwebwitch

WebmasterWorld Administrator httpwebwitch us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4278351 posted 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!

sample data:
number | letter | word
----
1 | a | one
2 | a | two
3 | a | three
4 | b | four
5 | b | five

my query:

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.

desired output:

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?

 

httpwebwitch

WebmasterWorld Administrator httpwebwitch us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4278351 posted 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

returns this:

3 one
5 four

isn't that weird.

httpwebwitch

WebmasterWorld Administrator httpwebwitch us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4278351 posted 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?

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4278351 posted 4:53 pm on Mar 8, 2011 (gmt 0)

That's pretty much the way I'd do it.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved