Total Query - How to get 'extra' field

[MarshaP]

I have a table that looks like this:

ResNum Room DischDate
001004 1139 10/03/08
001004 30 01/01/09
001007 208 03/03/08
001007 1335 12/17/08
001012 16 07/13/08
001012 1144 07/29/08
001012 1127 12/02/08

I want to find out what room each resident was in the last time they were here, along with the discharge date:

ResNum Room DischDate
001004 30 01/01/09
001007 1335 12/17/08
001012 1127 12/02/08

I am using this query:

SELECT ResMas.ResNum, Max(ResMas.DischDate) AS MaxOfDischDate, ResMas.Room
FROM ResMas
GROUP BY ResMas.ResNum;

I get a message that says Room needs to be part of an aggregate function.

When I tried to use the Last function for Room, it gave me 1139 as the last room for resident 001004. How do I get it to give me the room that corresponds to the latest date?

Thanks.

 

[PHV]

SELECT R.ResNum, M.MaxOfDischDate, R.Room
FROM ResMas R INNER JOIN (
SELECT ResNum, Max(DischDate) AS MaxOfDischDate FROM ResMas GROUP BY ResNum
) M ON R.ResNum = M.ResNum AND R.DischDate = M.MaxOfDischDate

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

 

[MarshaP]

That did it! Thanks for your help!