Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2008
    Posts
    3

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

    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.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  3. #3
    Join Date
    Dec 2008
    Posts
    3
    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.

  4. #4
    Join Date
    Dec 2008
    Posts
    3
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mike, that "solution" is for the highest bar in the table

    he wants the highest bar per foo

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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 );

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •