Using Max

[Mary]

I have a table with the following information:
StatusHistory Table
ICN 1, Status Date 1/1/2010, Status = A
ICN 1, Status Date 1/2/2010, Status = C
ICN 2, Status Date 1/3/2010, Status = P
ICN 2, Status Date 1/4/2010, Status = O

I am trying to write a SQL statement to select the most current status for
an ICN (for example, if ICN = 1, I would like to get a return value of "C"

I tried the following SQL statement, but it does not work. Any help would
be GREATLY appreciated.....THANKS!

SELECT status,Max([status date]) AS Latestdate FROM statushistory
where icn = '1'
GROUP BY status

This returns all status for ICN = 1

 

[KARL DEWEY]

Try this --
SELECT ICN, status, [status date]
FROM statushistory
where icn = '1' and [status date] = (Max([XX].[status date]) statushistory
AS [XX])
ORDER BY ICN;

 

[Ken Snell]

SELECT ICN, Status, [Status Date]
FROM StatusHistoryTable
WHERE [Status Date] =
(SELECT Max(T.[Status Date])
FROM StatusHistoryTable AS T
WHERE T.ICN = StatusHistoryTable.ICN);