Grouping to get Max Value of Sub Group

[Guest]

Greetings...I have Three Fields...Group, Sub-Group, and Value. Each Group
will have multiple Sub-Groups but each Sub-Group will only have one value.

What I am trying to do is get a query to output the Group with the Sub-Group
that has the largest Value.

I want the output to show all three fields and the number of records should
match the number of groups. Sounds easy enough but I can't seem to get the
right data.

Any help would be greatly appreciated.

-John

 

[Michel Walsh]

Hi,


SELECT a.group, a.subGroup, COUNT(*)
FROM myTable As a
GROUP BY a.group, a.subGroup
HAVING a.subGroup=(SELECT MAX(b.subGroup)
FROM myTable As b
WHERE b.group=a.group)



should do.

a.group, a.subGroup, COUNT(*) supply the groups, subGroups and number of
records in each group; the HAVING clause do the required filtering of
keeping just the max subgroup-value (NOT the subgroup with the max COUNT)
for each group (as I understand your question).



Hoping it may help,
Vanderghast, Access MVP