restrict aggregate query results? (1 Viewer)

[MS$DesignersRCretins]

I'd like to do a grouping query as follows. The source table has three numbers per record.

A    B    C
1   50    17
1  100    11
2   30    55
2   60    22

I would like to do a query that groups field A;
gives the max for field B;
gives the field C that corresponds to the max B for each A. That is, I WANT:
1 100 11
2 60 22
since those are the max B's. But if I go
SELECT A, Max(B), C
all 4 original records are returned, because I'm grouping C.
How can I get C in the output, but just the ones corresponding to max B?

All I can come up with is doing 2 passes:
select a,max(B)
and then left or right joining that output to the source table, thus preventing e.g. 1 50 17 from consideration.

Is there a more direct way?

Sorry for the lame thread title. I didn't know how to describe it succinctly.

 

[plog]

There's a different way to skin this cat, but I wouldn't call it more direct--it technically makes 2 passes at it as well. This method would use Dmax function nested inside a Dlookup.

Essentially you would Group BY A, get the MAX of B, then you would create a 3rd field like this for C:

Cvalue: Dlookup("[C]", "YourTableNameHere", "[A]=" & [A] & " AND = " & DMax("", "YourTableNameHere", "[A] = " & [A]))

I would keep the method you have now.

 

[MS$DesignersRCretins]

An offbeat solution occurred to me. Assuming C is assured to be less than a million, then
max (1e7*B+1e6+C)
and leave C out of the query. Then the right 6 positions are C; all but the right 7 are B.
Is that too wacky an approach?

 

[plog]

Yes. Plus you would still have to decode it (extract all positions out into correct fields) which means you still need 2 passes at it to get what you want.

 

[MS$DesignersRCretins]

(Regarding max (1e7*B+1e6+C))Yes. Plus you would still have to decode it (extract all positions out into correct fields)
which means you still need 2 passes at it to get what you want.

Actually I'd parse it in Excel, so it would be one pass in Access, since the C information would be captured. So this idea saves a pass, and a join, but adds work in Excel.

If anyone wants to see try all this, my original problem was, for the 3 field, 4 record table described earlier, that

SELECT A, Max(B), C
FROM Table1
GROUP BY A, C;
returns 4 records; It's grouping every C, not just the ones corresponding to highest B.

SELECT A, Max(B),
FROM Table1
GROUP BY A;
returns 2 records.as desired, but must be used as a subquery (or additional preliminary query); A and B from the first query or subquery must be joined to the original Table1.

SELECT A, Max(B), Max((10000000*)+1000000+[c]) AS foo
FROM Table1
GROUP BY A;
returns 2 records.as desired, and in a single pass, but C must still be parsed out. It's captured for later processing outside of Access.

I had hoped that somehow I could go
SELECT A, Max(B) AS MaxOfB, C
FROM Table1
GROUP BY A;
which is invalid ("You tried to execute a query that does not include the specified expression 'C' as part of an aggregate function"). That invalid query is what you get from removing C from the GROUP BY - that is, from the query builder, changing the "Total" line in the query builder from Group By to Expression. I was grasping for something clever (like SUM, which on first glance looked almost viable), or even a UDF. (The UDF didn't fool it. It still said an aggregate function was required.)

I think I was trying to defeat the laws of logic in this quest, but I guess that's why God made subqueries . If anyone has another practical or philosophical angle like plog's I'm interested, but I expect I'll just keep subquerying and joining meanwhile.

[edit: changed group by to max(B) in 3rd query above]

 

[tyjan10]

Test..............

 

[JHB]

You can do it with a UDF, but I don't think it is better or quicker as a sub query.
It is only to show it can be done.

Change the table name "Tabel3" to the table name you use.

The query:
SELECT A, Max(Tabel3.B) AS B, FindC([A],[B]) AS C
FROM Tabel3
GROUP BY A, FindC([A],[B])
HAVING (((FindC([A],[B])) Is Not Null));

Paste the following in a module.

Public Function FindC(A As Long, B As Long)
  Dim dbs As Database, rst As Recordset
  
  Set dbs = CurrentDb
  
  Set rst = dbs.OpenRecordset("SELECT A, Max(B) AS MaxOfB " _
  & "FROM Tabel3 WHERE A=" & "" & A & "" & " GROUP BY A")
  
  If rst![MaxOfB] = B Then
    Set rst = dbs.OpenRecordset("SELECT C " _
    & "FROM Tabel3 WHERE A=" & "" & A & "" & " AND B=" & "" & B & "")
    FindC = rst![c]
  End If
End Function