Max of Group

[Kev]

I am not a SQL expert by any stretch of the imagination, so any help is
appreciated. I have a simple problem, I think. I have a dataset that
consists of fields "Item", "Suffix", and "Length". I need to get a
resultant dataset that includes the HIGHEST Suffix for each Item along
with the length for that record. for example:
(the actual database includes many other fields and tables, but I pared
it down for the example)

ITEM SUFFIX LENGTH
1 A 39.5
1 B 39.0
1 C 39.7
2 A 38.3
3 A 39.0
4 A 39.5
4 B 38.8
.... etc

I need to get a result of:

ITEM SUFFIX LENGTH
1 C 39.5
2 A 38.3
3 A 39.0
4 B 38.8

I have tried GROUP BY, MAX(), HAVING, sub queries etc. Nothing works so
far. Any help is greatly appreciated.

Thanks,
Kev

 

[Chris L via AccessMonster.com]

You need to use a sub query, something like (air code):-

SELECT * FROM yourtable
WHERE yourtable.suffix=(SELECT MAX(t.suffux) FROM yourtable AS t WHERE t.
item=yourtable.item)

hth

Chris

 

[Van T. Dinh]

Try


****Untested****
SELECT Main.ITEM, Main.SUFFIX, Main.LENGTH
FROM [YourTable] AS Main
WHERE Main.SUFFIX =
(
SELECT Max(Sub.SUFFIX)
FROM [YourTable] AS Sub
WHERE Sub.ITEM = Main.ITEM
)
********

 

[Kev]

It looks like Chris' query might work. Mr. Dinh seems to have problems.
I could only get a result of 1 record for 1C.

Now to add all the other tables and fields. It might get complicated
in a hurry.

thanks,
kev

 

[John Spencer (MVP)]

SELECT M.Item, M.Suffix, M.Length
FROM YourTable as M
WHERE M.Length =
(SELECT Max(Tmp.Length)
FROM YourTable as Tmp
WHERE Tmp.Item = M.Item)

If there are ties, then you will get multiple records back.

Item Suffix Length
1 A 39.5
1 B 39.0
1 C 39.7 <--- This row returned
1 D 39.7 <--- And this row returned

That will return TWO records for Item 1,

 

[John Spencer (MVP)]

Whoops, cancel that. I misread your requirement.

 

[Van T. Dinh]

Actually, my SQL is essentially the same with Chris if you look at the 2 SQL
Strings carefully. The only differences are that I used Aliases and adding
blank lines for clarity.

If you get different results, you might have done something differently.