select each record by latest date!

Hi All,
I have one table which has CustomerID, ApplicationID and DateModified. Each of these CustomerID has more than 1 records based on different ApplicationID (so one customer can have 2-3 applicationID) and DateModified. I need to get one record each of those ApplicationID associated with the given CustomerID based on latest DateModified. It looks very simple but I can't seem to figure out.

Example:
CustomerID ApplicationID DateModified
1 100 9/20/06
1 200 9/21/06
2 500 8/20/06
2 600 9/10/06
2 700 9/23/06

Output:
CustomerID ApplicationID DateModified
1 200 9/21/06
2 700 9/23/06

select *
from table t
where DateModified = (select max(DateModified) from table x where x.CustomerID = t.CustomerID)



KH

quote:Originally posted by khtan



select *
from table t
where DateModified = (select max(DateModified) from table x where x.CustomerID = t.CustomerID)



KH

Thanks Khtan for the quick reply. But, I am getting more records than expected with this query. I wanted to return 155 records of latest ApplicationID based on 155 CustomerID but it's returning more than 1605 records. Am I missing something here?

select *
from table t
where DateModified = (select max(DateModified) from table x where x.CustomerID = t.CustomerID And CustomerID IN (list of 155 CustomerIDs))

I think it was data issue. The query seem to be working fine. Thanks khtan for the query.

If you have more than one record for the client with the same DateModified, then you will get all matching rows returned, not just one.

This version filters on both the most recent ApplicationId and the most recent DateModified:

SELECT * FROM table t
WHERE t.ApplicationId =
(SELECT TOP 1 y.ApplicationId FROM table y WHERE y.CustomerID = t.CustomerID
AND y.DateModified = (SELECT MAX(DateModified) FROM table x WHERE x.CustomerID = t.CustomerID)
ORDER BY y.applicationId DESC)

This is assuming that ApplicationId is a numeric column with a sequential numbering system.