get max value for each account

[johnboy7676]

Struggling with what I thought would be a simple query, each owner
will have at least Account, probably multiple Accounts. I need to
select the Account# for each OwnerID, that has the highest acres.

OwnerID Account acres
1 55A 2.0
1 27B 40.0
2 10A 25.0
3 8B 16.0
3 77C 45.0
3 194 20.0


So my result would be:

1 27B 40.0
2 10A 25.0
3 77C 45.0

(there are around a thousand owners/accounts)

I thought this would be snap, but am struggling with it, any pointers?

Thanks, John

 

[John Spencer MVP]

Easiest way is a two query approach.

Query One (A totals query that gets the max acreage for each owner):
SELECT OwnerID, Max(Acres) as BigAcre
FROM SomeTable
GROUP BY OwnerID

Query Two uses that saved query and the table (joining id to id and acre to acre)
SELECT S.*
FROM SomeTable as S INNER JOIN SavedQuery as Q
ON S.OwnerID = Q.OwnerID
AND S.Acres = Q.BigAcre

If your field and table names consist of only letters, numbers, and the
underscore character, you can usually do that in one query
SELECT S.*
FROM SomeTable as S INNER JOIN
(SELECT OwnerID, Max(Acres) as BigAcre
FROM SomeTable
GROUP BY OwnerID)as Q
ON S.OwnerID = Q.OwnerID
AND S.Acres = Q.BigAcre

You could also use a correlated subquery in a where clause
SELECT S.*
FROM SomeTable
WHERE Acres = (SELECT Max(Acres)
FROM SomeTable As Temp
WHERE Temp.OwnerID = S.OwnerID)

Correlated sub-queries tend to be slow, but the results will be updatable.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

Struggling with what I thought would be a simple query, each owner
will have at least Account, probably multiple Accounts. I need to
select the Account# for each OwnerID, that has the highest acres.

OwnerID Account acres
1 55A 2.0
1 27B 40.0
2 10A 25.0
3 8B 16.0
3 77C 45.0
3 194 20.0


So my result would be:

1 27B 40.0
2 10A 25.0
3 77C 45.0

(there are around a thousand owners/accounts)

Here's a way to do that:

SELECT T.*
FROM table As T
INNER JOIN (SELECT X.Account, Max(X.Acres) As MaxAcres
FROM table As X
GROUP BY X.Account) As Y
ON T.Account = Y.Account And T.Acres = Y.MaxAcres

 

[johnboy7676]

Here's a way to do that:

SELECT T.*
FROM table As T
INNER JOIN (SELECT X.Account, Max(X.Acres) As MaxAcres
FROM table As X
GROUP BY X.Account) As Y
ON T.Account = Y.Account And T.Acres = Y.MaxAcres

I get the whole recordset. But, I don't see any reference to OwnerID?

Thanks
John

 

[johnboy7676]

Inline...................

Easiest way is a two query approach.

Query One (A totals query that gets the max acreage for each owner):
SELECT OwnerID, Max(Acres) as BigAcre
FROM SomeTable
GROUP BY OwnerID

Query Two uses that saved query and the table (joining id to id and acre to acre)
SELECT S.*
FROM SomeTable as S INNER JOIN SavedQuery as Q
ON S.OwnerID = Q.OwnerID
AND S.Acres = Q.BigAcre




If your field and table names consist of only letters, numbers, and the
underscore character, you can usually do that in one query
SELECT S.*
FROM SomeTable as S INNER JOIN
(SELECT OwnerID, Max(Acres) as BigAcre
FROM SomeTable
GROUP BY OwnerID)as Q
ON S.OwnerID = Q.OwnerID
AND S.Acres = Q.BigAcre

I tried the above, and it seems to work. I had thought of trying to do
something like this (although wasn't sure how to go about it), but was
afraid that 2 Accounts might have the exact same acres. In that case,
for my purposes, I don't care which account it selects, but was
concerned that perhaps both accounts might be selected.

Anyway, this seems to work, Thanks.

John

 

[Marshall Barton]

I get the whole recordset. But, I don't see any reference to OwnerID?

Sorry, I used Account where I should have used OwnerID. It
was supposed to be the same as John's one query solution.

 

[johnboy7676]

Sorry, I used Account where I should have used OwnerID. It
was supposed to be the same as John's one query solution.

OK, thanks.
John

 

[KALEIGH]

thx

OK, thanks.
John

 

[John Spencer MVP]

You are correct that in the case of the same owner having two accounts with
the same maximum acreage you would get two records back. You can modify the
query to Account for that by turning the query into a Totals (aggregate) query.

SELECT S.OwnerID
, First(S.Account) as TheAccount
, S.Acres
FROM SomeTable as S INNER JOIN SavedQuery as Q
ON S.OwnerID = Q.OwnerID
AND S.Acres = Q.BigAcre
GROPU BY S.OwnerID, S.Acres

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County