Max and GroupBy in query 1

[eadiem]

I am trying to create a query which selects the record of each ID with a maximum value. I need to use 3 fields from my table:
1. ID (foreign key)
2. Variety
3. Acres
I want to select the record for each ID which has the most acres of a given variety. So if my table looks like this:

ID1 Blue 5
ID1 Red 3
ID1 Green 2
ID2 Red 10
ID2 Purple 15

I need it to return this:

ID1 Blue 5
ID2 Purple 15

I can create a query which returns the correct acreage but can't get it to display the name of the variety too...I get an "aggregate function" error. If I include use GroupBy to include Variety, the query no longer returns the Max, but returns all values.

Here is the query that doesn't include Variety name.

SELECT Varieties.ID, Max(Varieties.Acres) AS MaxOfAcres
FROM Varieties
GROUP BY Varieties.ID;


Any suggestions?

Thank you,
Eadie

 

[r937]

select ID 
     , Variety
     , Acres
  from Varieties as V
 where Acres =
     ( select max(Acres)
         from Varieties
        where Variety
            = V.Variety  )

rudy

http://r937.com/

SQL Consulting

 

[PHV]

Have you tried this ?
SELECT A.* FROM Varieties A INNER JOIN
(SELECT ID, Max(Acres) AS MaxOfAcres FROM Varieties GROUP BY ID) M
ON (A.ID = M.ID) AND (A.Acres = M.MaxOfAcres);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eadiem]

Thank you PHV, your idea worked perfectly!

To Rudy - thanks for responding but your query only returned the single record with the most acreage of any given variety, so there was only one record for each variety...not quite what I had in mind!

Eadie

 

[r937]

so right

i misread the question, sorry

nice one, PHV

rudy

http://r937.com/

SQL Consulting