Finding Max of a DB field and then return whole record grouping on one column

[Paul W Smith]

I have a table as below:

X 210 5 TRUE A
Y 220 7 FALSE B
X 210 3 TRUE D
Y 219 1 TRUE B
Z 215 4 TRUE C
Z 212 5 FALSE D
X 209 1 TRUE A
Y 220 2 TRUE A
Z 221 3 TRUE B

What I am looking to return is the following, whether this by one
complicated query or a series of Queries:

X 210 3 TRUE D
Y 220 2 TRUE A
Z 221 3 TRUE B

I am trying to group on the first column and then find the Max of the second
column, and where this is equal, to use the record which has the Max of the
third column.

I do not wish to use any custom VBA functions.

 

[smartin]

I have a table as below:

X 210 5 TRUE A
Y 220 7 FALSE B
X 210 3 TRUE D
Y 219 1 TRUE B
Z 215 4 TRUE C
Z 212 5 FALSE D
X 209 1 TRUE A
Y 220 2 TRUE A
Z 221 3 TRUE B

What I am looking to return is the following, whether this by one
complicated query or a series of Queries:

X 210 3 TRUE D
Y 220 2 TRUE A
Z 221 3 TRUE B

I am trying to group on the first column and then find the Max of the second
column, and where this is equal, to use the record which has the Max of the
third column.

I do not wish to use any custom VBA functions.

I'm not following your logic here:

Within X, max(col 2) is 210, within that, max(col 3) is 5 (not 3)
Within Y, max(col 2) is 220, within that, max(col 3) is 7 (not 2)
Within Z, max(col 2) is 221, within that, max(col 3) is 3 (ok fine)

Did you mean

I am trying to group on the first column and then find the Max of the second
column, and where this is equal, to use the record which has the /last/ of the
third column.

Is so, can you ensure the order of the records is consistent? (Note:
Selecting a table without ordering will not necessarily produce
consistent results.)

 

[Paul W Smith]

Apologies.

I did mean Group on Column 1, Max of col 2, and where this is equal use the
record which has the Min of col 3.... to be exact where column 2 and col 3
are equal use the record where Col 4 is true.

I dod not understand your question about ordering. If it helps the records
in the table are added to on a weekly basis, so thre newer records are at
the end of the table (bottom).

I appreciate you taking the time to assist.

 

[John Spencer]

One method is to use three queris.

First Query:
SELECT Col1, Max(Col2) as Col2Big
FROM YourTable
GROUP BY Col1

Second query:
SELECT YourTable.Col1, YourTable.Col2, Max(Col3) as Big3
FROM YourTable INNER JOIN Query1
On YourTable.Col1 = Query1.Col1
AND YourTable.Col2 = Query1.Col2Big

Third Query
SELECT YourTable.*
FROM YourTable INNER JOIN Query2
ON YourTable.Col1 = Query2.Col1
AND YourTable.Col2 = Query2.Col2
AND YourTable.Col3 = Query2.Big3

If your field and table names consist of only letters, numbers, and
underscore characters, you can do that all in one query using two
subqueries.

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

 

[Paul W Smith]

Thank you for your solution below.

I would be interested in the definiton of the one query with two subqueries
method?

PWS

 

[John Spencer]

SELECT YourTable.*
FROM YourTable INNER JOIN
(SELECT YourTable.Col1, YourTable.Col2, Max(Col3) as Big3
FROM YourTable INNER JOIN
(SELECT Col1, Max(Col2) as Col2Big
FROM YourTable
GROUP BY Col1) as Query1
On YourTable.Col1 = Query1.Col1
AND YourTable.Col2 = Query1.Col2Big) as Query2
ON YourTable.Col1 = Query2.Col1
AND YourTable.Col2 = Query2.Col2
AND YourTable.Col3 = Query2.Big3


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

 

[smartin]

Apologies.

I did mean Group on Column 1, Max of col 2, and where this is equal use the
record which has the Min of col 3.... to be exact where column 2 and col 3
are equal use the record where Col 4 is true.

I dod not understand your question about ordering. If it helps the records
in the table are added to on a weekly basis, so thre newer records are at
the end of the table (bottom).

I appreciate you taking the time to assist.

No worries, Paul. If you are looking for min or max, order is not an an
issue. Since you restated your question in those terms, you should not
have a problem.

FWIW If you invoke the First() function in Access, the order of rows is
significant, but this can be controlled by a query that ensures a
meaningful order of records is examined. OTOH querying a table without
specifying order can produce unpredictable results.

HTH