Need help understanding Max function in SQL

[DancingDave]

Can you help me understand why the following doesn't produce a recordset with just one record per Name ID? And also show me how to change it so it does? Thanks.
[CODE=SQL]SELECT [MyTable].[Name ID], Max([MyTable].Code) AS MaxOfCode, [MyTable].[Active Date]
FROM [MyTable]
GROUP BY [MyTable].[Name ID], [MyTable].[Active Date];[/CODE]
I've researched this quite extensively and am just not "getting it". :-( Note: DISTINCT doesn't help. Here is a snippet of the resulting recordset. I expect there should only be 4 records shown, not 6.

Name ID   MaxOfCode  Active Date
AMADNI01  CT-005     03/22/99
ANANJA01  CT-005     09/01/92
ANANJA01  CT-007     05/22/94
ANDRDJ01  CT-005     08/01/92
ANDRDJ01  CT-006     11/11/94
AQUIDE01  CT-005     05/03/93

[FishVal]

Hello, Dave.

Could you please post a desired recordset you want the above recordset to transform to?

Kind regards,
Fish.

[DancingDave]

Cool. No Problem. Thanks Fish! There are two separate issues I'm trying to tackle. Here is a sample raw recordset, followed by the desired recordset:

Name ID   MaxOfCode  Active Date
AMADNI01  CT-005     03/22/99
ANANJA01  CT-005     09/01/92
ANANJA01  CT-007     05/22/03   (that is 2003)
ANDRDJ01  CT-005     08/01/92
ANDRDJ01  CT-006     11/11/94
AQUIDE01  CT-005     05/03/93
AQUIDE01  CT-005     07/07/03   (that is 2003)

The recordset below shows both the "Max(Code)" and the "Max(Active Date) for the Max(Code)" for each person. My two separate needs are:
1) To just find the Max(Code) for each person without regard to the Active Date
2) To find the record with Max(Code) and Max(Active Date) for the Max(Code) for each person.
I state these separately only because I suspect 2) is a more complicated query. If 2) is super hard, then I might have the data integrity to key off of Max(Active Date) ignoring Code all together, but I'll have to look into that. Any help you can offer for either 1) or 2) is greatly appreciated!!!!

Name ID   MaxOfCode  Active Date
AMADNI01  CT-005     03/22/99
ANANJA01  CT-007     05/22/03
ANDRDJ01  CT-005     08/01/92
AQUIDE01  CT-005     07/07/03

[FishVal]

Ok, Dave.

If I've understood your correctly a record returning by the query should contain:

• unique [Name ID]
• Max([MaxOfCode]) associated with [Name ID]
• Max([Active Date]) associated with [NameID] and Max([MaxOfCode])

I would suggest you the following:

First query returns recordset of [Name ID] and Max([MaxOfCode])
qryMaxCode
[code=sql]
SELECT [MyTable].[Name ID], Max([MyTable].Code) AS MaxOfCode
FROM [MyTable]
GROUP BY [MyTable].[Name ID];
[/code]


Second query joins the first one with [MyTable] on [NameID] and

 and groups records to return Max([Active Date])

[code=sql]
SELECT [MyTable].[Name ID], [qryMaxCode].[MaxOfCode], Max([MyTable].[Active Date]) AS MaxDate
FROM [MyTable] INNER JOIN [qryMaxCode] ON [qryMaxCode].[Name ID]=[MyTable].[Name ID] AND [qryMaxCode].[MaxOfCode]=[MyTable].Code
GROUP BY [MyTable].[Name ID], [qryMaxCode].[MaxOfCode];

Regards,
Fish

P.S. The code was not tested.

[DancingDave]

Thank You!!! I was thinking it could all be accomplished in one query. The solution you offer works wonderfully. I appreciate the assistance. :-)
Thanks,
Dave

[FishVal]

You are quite welcome.

[NeoPa]

For your interest, and to avoid having to save the subquery separately, this can also be done using subqueries (See Subqueries in SQL).
[CODE=SQL]SELECT tMT.[Name ID],
subQ.MaxOfCode,
Max(tMT.[Active Date]) AS MaxDate
FROM MyTable AS tMT INNER JOIN
(SELECT [Name ID],
Max(

) AS MaxOfCode
      FROM [MyTable]
      GROUP BY [Name ID]) AS subQ
  ON tMT.[Name ID]=subQ.[Name ID]
 AND tMT.Code=subQ.MaxOfCode
GROUP BY tMT.[Name ID],
         tMT.Code