sql aggregate query

[ras]

My table

ID - Autonumber Field
Patientid - This can have a number of records with the same number
dDate is the date of the record and can be duplicated.

I need to create a new table with the max of date for each unique
patient id.

So far not a problem.

SELECT TblCurrentEncounter.Patientid, Max(TblCurrentEncounter.dDate)
AS MaxOfdDate
FROM TblCurrentEncounter
GROUP BY TblCurrentEncounter.Patientid;

But the kicker is that i need the id of the row containing the max of
date.

As soon as i try to add ID to the query I get error about not being
part of the aggregate function.

Any ideas would be appreciated.

 

[Ken Snell \(MVP\)]

Use a subquery to get the maximum date for each ID, and use it in the WHERE
clause:

SELECT ID, dDate AS MaxOfdDate
FROM TblCurrentEncounter
WHERE dDate =
(SELECT Max(T.dDate) As Md
FROM TblCurrentEncounter AS T
WHERE T.ID = TblCurrentEncounter.ID);

 

[Arvin Meyer [MVP]]

Try:

SELECT ID, Patientid, Max(TblCurrentEncounter.dDate) AS MaxOfdDate
FROM TblCurrentEncounter
GROUP BY ID, Patientid;

 

[David W. Fenton]

Use a subquery to get the maximum date for each ID, and use it in
the WHERE clause:

SELECT ID, dDate AS MaxOfdDate
FROM TblCurrentEncounter
WHERE dDate =
(SELECT Max(T.dDate) As Md
FROM TblCurrentEncounter AS T
WHERE T.ID = TblCurrentEncounter.ID);

More correctly, that's called a *correlated* subquery, in that each
row is correlated with a subquery whose results are specific to that
row.