Here's what the SQL statement would look like:
select top 1 *
where emailto = 'email@example.com'
and EmailStatus = 3
order by EmailLastSendAttempt desc
So essentially you need to do something like this in code:
var email = CMS.EmailEngine.EmailInfoProvider.GetEmailInfos("EmailTo = 'firstname.lastname@example.org' AND EmailStatus = 3", "EmailLastSendAttempt DESC", 1, "");
This should get you an
InfoDataSet<EmailInfo> you can iterate through.