Group by with Max Query 1

[dbero]

Hello:

I am trying to run a query that returns the item with the max date, for each policy #. I also want additional fields returned, but not grouped by. The example below attempts to explain. can this be done?

Sample dataset

Policy Requirement Received Date Submission Mode
1234 APS 5/1/2007 Paper
1234 MVR 4/22/2007 Paper
1234 ALQ 4/15/2007 Paper
789 ALQ 3/15/2007 Scan
789 MVR 3/16/2007 Scan
789 APS 4/1/2007 Scan

Return from Query
Policy Requirement Received Date Submission Mode
1234 APS 5/1/2007 Paper
789 APS 4/1/2007 Scan

 

[PHV]

Something like this ?
SELECT A.Policy, A.Requirement, A.[Received Date], A.[Submission Mode]
FROM [Sample dataset] AS A INNER JOIN (
SELECT Policy, Max([Received Date]) AS LastDate FROM [Sample dataset] GROUP BY Policy
) AS L ON A.Policy = L.Policy AND A.[Received Date] = L.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

How about:

[tt]SELECT A.Policy, A.Requirement, A.[Received Date], A.[Submission Mode]
FROM tblTable A
WHERE A.[Received Date] In
(Select Max([Received Date])
from tblTable T
where T.policy=A.policy)[/tt]