Displaying last status by date criteria

[zuza]

Dear all,
I have a status table where the DS# are entered as history like :
DS# Status date
DS000001 --- claim received -- 12/03/2003
DS000001 --- notification completed -- 12/06/2003
DS000002 --- claim received -- 21/02/2004
DS000002 --- HPCC decisions -- 20/03/2004

They're asking me to display DS # ONCE with the last status(that means with the max date) nearby.

I hope you got the point.

Rgds,
ZuZa.

 

[FesterSXS]

This might do the trick...

strSQL = "SELECT DISTINCT DS, Status from myStatusTable ORDER BY [date] DESC;"

Tony

http://www.SpiraxSarco.com

 

[amorous]

Try this:

SELECT t1.*
FROM [MyTable] t1
WHERE t1.[date] In (select top 1 t2.[date] from [MyTable] t2 where t2.[DS#] = t1.[DS#] ORDER BY t2.[date] DESC)


-VJ