I have a large data-set of emails sent and status-codes.

ID Recipient           Date       Status
 1 [email protected] 01/01/2010      1
 2 [email protected] 02/01/2010      1
 3 [email protected]    01/01/2010      1
 4 [email protected]    02/01/2010      2
 5 [email protected]    03/01/2010      1
 6 [email protected]  01/01/2010      1
 7 [email protected]  02/01/2010      2

In this example:

• all emails sent to someone have a status of 1
• the middle email (by date) sent to them has a status of 2, but the latest is 1
• the last email sent to others has a status of 2

What I need to retrieve is a count of all emails sent to each person, and what the latest status code was.

The first part is fairly simple:

SELECT Recipient, Count(*) EmailCount
FROM Messages
GROUP BY Recipient
ORDER BY Recipient

Which gives me:

Recipient           EmailCount
[email protected] 2
[email protected]    3
[email protected]  2

How can I get the most recent status code too?

The end result should be:

Recipient           EmailCount LastStatus
[email protected]          2          1
[email protected]             3          1
[email protected]           2          2

Thanks.

(Server is Microsoft SQL Server 2008, query is being run through an OleDbConnection in .Net)