How to write this query: group "abc" with largest "xyz"? I am stuck!!! Help please :)

sqlBrain · #1 (**permalink**) 12-15-08, 06:41

Say you have a table with this data in it:

foo | bar | uid
++++++++++++++++++
Blah1 | 100 | 1
Blah1 | 200 | 2
Blah2 | 100 | 3
Blah2 | 200 | 4
++++++++++++++++++

And you want to GROUP by *foo* that has the highest *bar*.

Eg, with the above data, you are wanting this:

foo | bar | uid
++++++++++++++++++
Blah1 | 200 | 2
Blah2 | 200 | 4
++++++++++++++++++

*foo* is grouped with the highest *bar*.

How do you do it?

If you think that you could ORDER by *bar* and then GROUP *foo*, you can't, as GROUP is run first, making your ORDER pointless.

If you think that you could use MAX(*bar*) and then GROUP by *foo*, you can't, as the *uid* will be bogus (and all other data in the column). You want the row with the highest *bar*, not just the highest *bar* shoved into the first (wrong) row!

I'm really stuck. Any ideas will be greatly appreciated!!!

Cheers!
Robin.

mike_bike_kite · #2 (**permalink**) 12-15-08, 07:42

Dear sqlBrain

First write some SQL to get the max *bar* value. Then write a query to return all records where the *bar* value is equal to this value. You'll end up with the 2 bits of SQL like this :

Code:

2nd SQL statement
where bar = ( 1st SQL statement )

Mike

PS Good luck with your homework

sqlBrain · #3 (**permalink**) 12-15-08, 07:49

Ah, this isn't my homework, that's really offensive, mate. I am doing this for my own personal website listing frame designs that have a year, and I need the latest year only (listing other years below), if that makes sense.

Thanks for your help, though. But please... go abuse someone else, that actually deserves it, perhaps.

Have a nice day.

sqlBrain · #4 (**permalink**) 12-15-08, 08:17

Sorry for being short above, but saying that I'm some kid asking for help with my homework struck a chord with me. Moving right along now...

Unfortunately I don't think your suggestion will work. As there is not a single max *bar* value. Some of my grouped *foos* have different max *bars*.

So imagine:

foo | bar | uid
++++++++++++++++++
Blah1 | 100 | 1
Blah1 | 200 | 2
Blah2 | 500 | 3
Blah2 | 880 | 4
++++++++++++++++++

Assuming I understand your post correctly, you are suggesting to finding the single highest *bar* value and returning all rows with that value. With the above data (with 880 and 200 being the highest in the grouped *foos*), only one row would be returned... whereas I want the following results:

foo | bar | uid
++++++++++++++++++
Blah1 | 200 | 2
Blah2 | 880 | 4
++++++++++++++++++

Is there a way? Any help much appreciated. I've spent days on this issue now getting nowhere, and I'm only now asking for help.

Cheers guys.

r937 · #5 (**permalink**) 12-15-08, 08:39

you'll have to forgive mike, he has not yet reached the stage where he can spot a homework question with any sort of accuracy

we get homework questions here all the time, so i hope you can understand that we are naturally suspicious

anyhow, to get back to your problem, there are at least eleven ways to do it (the title of the article says 10, but read it carefully to see the 11th, which i submitted)

interestingly enough, a couple of them are actually described in da manual

mike_bike_kite · #6 (**permalink**) 12-15-08, 08:40

Quote:

this isn't my homework, that's really offensive, mate

It's not abuse, it's simply suspicion that I'm wasting my own time doing other peoples homework. Many of the posts at the moment are from kids just posting their homework on the forum and expecting us to do it. their queries are usually on trivial data sets with nonsensical field names (foo, bar etc). Usually their posts are the first (and often only) post they've ever made (yours is post 1). Hence I'm suspicious

Quote:

Is there a way? Any help much appreciated. I've spent days on this issue now getting nowhere, and I'm only now asking for help.

My first response did show how to solve the problem but seeing as you're not a student then the full answer would be :
Code:
select * from MyTab
where bar = ( select max(bar) from MyTab )

Mike

r937 · #7 (**permalink**) 12-15-08, 08:44

mike, that "solution" is for the highest bar in the table

he wants the highest bar per foo

mike_bike_kite · #8 (**permalink**) 12-15-08, 08:51

When all the field names are completely meaningless it's difficult to work out whether you've answered the query correctly or not - it certainly looked like what he was after but then I was typing as quickly as possible hoping to get my response out before you had time to get in a sarcastic remark - sadly I failed

mike_bike_kite · #9 (**permalink**) 12-15-08, 15:54

I thought I'd better put in the correct SQL but no doubt it can be improved :

Code:

create table MyTab (
        foo     varchar(10),
        bar     int,
        uid     int
);

insert MyTab values ( "Blah1",100,1 );
insert MyTab values ( "Blah1",200,2 );
insert MyTab values ( "Blah2",500,3 );
insert MyTab values ( "Blah2",880,4 );

select  t1.*
from    MyTab t1
where   t1.bar = (
                select  max(bar)
                from    MyTab t2
                where   t2.foo=t1.foo );

r937 · #10 (**permalink**) 12-15-08, 16:01

why is it that you doubt yourself so much?

that works, but the correlated subquery can be quite slow

try the join-to-derived-table method

Internet Services

Web Development

Digital Marketing

Consumer Tech