Re: Grouping to get Max Value of Sub Group

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"H0MELY" <H0MELY@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6C0D2541-F9F2-45A9-AFC2-F311DDE46FA7@xxxxxxxxxxxxxxxx
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


.