use max & group by to retreive a 3rd field 1

[longhair]

afternoon all,
most likely a simple question but I have been unable to crack it.
have 1 table:
item - desc - custitem - retail - countofretail

what i need is the 5 above fields grouped by item and the max of countofretail
what i have so far:

SELECT [item], First([Desc) AS FirstOfDesc, First([custitem]) AS FirstOfCust, Max([countofretail]) AS [MaxOfCountOfRetail]
FROM [A]
GROUP BY [A].[item]
ORDER BY [A].[item];

this returns the correct number of records. if I add the retail field into the mix it returns all records in the table.
any suggestions?
thanks.
regards,
longhair

 

[PHV]

What is the PrimaryKey of your A table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[longhair]

PHV,
no primary unique key of the table but i would consider item to be the primary non unique since that is what all grouping is based on.
regards,
longhair

 

[PHV]

What about this ?
SELECT A.item, A.Desc, A.custitem, A.retail, B.MaxOfCountOfRetail
FROM A INNER JOIN (
SELECT item, Max(countofretail) AS MaxOfCountOfRetail FROM A GROUP BY item
) AS B ON A.item = B.item AND A.countofretail = B.MaxOfCountOfRetail
ORDER BY A.item

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[longhair]

PHV,
thanks. worked like a charm. a star for you.
new it was something simple and this was the road that i was going down, but my sql is not on par with my other languages.
regards,
longhair