Max of value from DataGroup2 within DataGroup1

[Guest]

Hello -

I have a table with data as follows below.

I am trying to build a query that will give me the record with MAX of
specDiffMax [value] for each TestFreq [DataGroup 2] within each TestNum
[DataGroup1].

ID TestNum TestFreq specDiffMax
--------------------------------------------
4889683 Test 1 1710 3.669998
5123289 Test 1 1710 2.882999
4817314 Test 1 1710 3.102001
5134007 Test 1 1710.2 3.573002
4896056 Test 1 1710.2 3.355
4914480 Test 1 1710.2 3.515999
4889685 Test 2 1710.4 3.333
4896057 Test 2 1710.4 3.450001
4914481 Test 2 1710.4 3.477001
5134008 Test 2 1710.4 3.608002
4914482 Test 2 1710.6 3.450001
4896058 Test 2 1710.6 3.417999
4889686 Test 2 1710.6 3.720001
etc ...

RESULT REQUIRED:

ID TestNum TestFreq specDiffMax
--------------------------------------------
4889683 Test 1 1710 3.669998
5134007 Test 1 1710.2 3.573002
5134008 Test 2 1710.4 3.608002
4889686 Test 2 1710.6 3.720001

Any help greatly appreciated.

thanks
sandy

 

[Guest]

Use these two queries ---
Sandy_X
SELECT Sandy.TestNum, Sandy.TestFreq, Max(Sandy.specDiffMax) AS
MaxOfspecDiffMax
FROM Sandy
GROUP BY Sandy.TestNum, Sandy.TestFreq;

SELECT Sandy.ID, Sandy.TestNum, Sandy.TestFreq, Sandy.specDiffMax
FROM Sandy INNER JOIN Sandy_X ON (Sandy.specDiffMax =
Sandy_X.MaxOfspecDiffMax) AND (Sandy.TestFreq = Sandy_X.TestFreq) AND
(Sandy.TestNum = Sandy_X.TestNum);