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?Try sub query like:

select f1, f2 from table1 where f3 = (select max(f3) from table1)|||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|||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: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|||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...|||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.|||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?|||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|||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... :rolleyes:

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

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...|||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?|||:D

Hey it's FRIDAY!

Let the happy hour(s) begin!

If it's hot, then it must be time for blender drinks!|||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|||wasn't blender the name of that robot in futurama?

[hic] yep, it's finally friday

i started last night!!!|||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...|||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.|||http://www.dbforums.com/showthread.php?t=989246&page=28&pp=15|||Go to the bottom of the forum page (http://www.dbforums.com/forumdisplay.php?f=7), and the Who's Viewing is shown there.

Go to Top posters (http://www.dbforums.com/memberlist.php?&order=DESC&sort=posts&pp=30<r=) to see who's listed there.

-PatP|||Very value added... :rolleyes:

You know, a few years from now, I'll roll my eyes at someone here...And then you guys will roll your eyes at me for rolling my eyes at them...

Screw it, bring me my guiness, waiter!!|||dude, it's not called guiness

a real guinness lover would kill you for that|||:rolleyes:|||What, did you spill beer on the keys?|||any version.