Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Group by help

  1. #1
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46

    Unanswered: Group by help

    I have a table with three columns: "item_number", "unit_of_measure", "number_of_units".
    Data example:
    "0001","UNIT","1"
    "0001","1/2 CASE","12"
    "0001","CASE","24"

    I want to do a select so I can get only the "item_number" and "unit_of_measurement", when the "number_of_units" is the highest
    example:
    "0001","CASE"

    any ideas?

  2. #2
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    54
    Try sub query like:

    select f1, f2 from table1 where f3 = (select max(f3) from table1)

  3. #3
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    hmm I tried it but it doesn't work in my case, because different "item_numbers" have different "units_of_measure" and different "number_of_units", example:

    0001,UNIT,1
    0001,1/2 CASE,12
    0001,CASE,24
    0002,UNIT,1
    0002,DOZEN,12
    0002,BOX,36

    and with that select I only get the items that have the same maximum "number_of_units" as the first item

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This smells like homework, but I've already been busted on that once today so I'll give you the benefit of the doubt. I'd use:
    Code:
    SELECT item_number, unit_of_measure
       FROM table1 AS a
       WHERE a.number_of_units = (SELECT Max(b.number_of_units)
          FROM table1 AS b
          WHERE  b.item_number = a.item_number)
    -PatP

  5. #5
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62

    alias?

    Only you wouldn't use the alias's?? Right blindman? Or whoever that was that flamed me for doing that a while back. Can't seem to find it now...

  6. #6
    Join Date
    May 2004
    Posts
    3

    group by

    Try this:
    SELECT item_number, unit_of_measure
    FROM table1
    WHERE (item_number, number_of_units) IN (SELECT item_number, MAX(number_of_units)
    FROM table1
    GROUP BY item_number)
    yabu.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yabu, that's very pretty

    row expressions are a very useful addition to the sql standard

    but the more important question is: what version of sql server does that work in?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by AnSQLQuery
    Only you wouldn't use the alias's?? Right blindman? Or whoever that was that flamed me for doing that a while back. Can't seem to find it now...
    Just curious, but how were you thinking that you'd do that without using an alias? You could leave them defaulted in most of that code, but I'm thinking that the inner WHERE clause would be quite a pig to code!

    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by AnSQLQuery
    Only you wouldn't use the alias's?? Right blindman? Or whoever that was that flamed me for doing that a while back. Can't seem to find it now...

    Very value added....

    Pat, he's refering to The blind dudes obsession with fully qualified table names as a reference.....

    Instead of a as an alias, he's saying he'd prefer

    Code:
    SELECT 
    [This Is my Table and I dont care how long the name is and type it over and over].Col1
      FROM [This Is my Table and I dont care how long the name is and type it over and over]
     WHERE [This Is my Table and I dont care how long the name is and type it over and over].Col2 = 'A'
    I think I was flamed once or twice...but I can't remeber..

    Oh that's right....I don't care...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ok, you guys have talked about me long enough!

    Yeah, you gotta us an alias for the subquery. I'd name it something more descriptive than "b", and I wouldn't us an alias for a direct reference to table1, but I understand Pat was just giving an example.

    Is it hot in here, or is it just me?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322


    Hey it's FRIDAY!

    Let the happy hour(s) begin!

    If it's hot, then it must be time for blender drinks!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Ok, you guys have talked about me long enough!
    Oh says who ??? We talk about you all the time, just not often in public!

    -PatP

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wasn't blender the name of that robot in futurama?

    [hic] yep, it's finally friday

    i started last night!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    wasn't blender the name of that robot in futurama?

    [hic] yep, it's finally friday

    i started last night!!!

    I think it was bender....

    Ya, know...as in going on a......

    And it's tough to talk behind your back now blind dude...now that the removed the who's viewing the forum..

    Oh, wait...I forgot you're blind...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Only in the metaphorical sense. You know, like justice?

    I can't find the page where the top posters are listed either, and it irks me that you have to wait 90 seconds between searches. El sucko.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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