html code

How to Choose the Initial Record from a Set of Multiple Records

I have a custom sObject which has among others the following fields: email__c (email), some_date__c (date), external_id__c (integer) and criteria__c (string). There are multiple records for this sObject that have the same value for email__c.

If an email address returned in the results has multiple records we want to select the record with the most recent date. If there is a tie on the date then select the record with the greatest external id. However we only want to return the selected record if it matches the criteria.

Below is how I have been able to do this with Apex code but I am wondering if there is a way I can accomplish the same with a single SOQL query or with simpler code. Any suggestions?

public List<Project__c> getRecords(String criteria) {
    List<String> emails = new List<String>();
    Map<String, Project__c> emailProjectMap = new Map<String, Project__c>();
    List<Project__c> retVal = new List<Project__c>();

    for( Project__c project : [ Select email__c From Project__c 
                                Where criteria__c = :criteria ] ) { 
       emails.add(project.email__c);
    }
    for( Project__c project : [ Select email__c, some_date__c, external_id__c, criteria__c
                                From Project__c Where email__c in :emails 
                                Order by some_date__c, external_id__c ] ) {
       emailProjectMap.put(project.email__c, project);
    }
    for( Project__c project : emailProjectMap.values() ) {
        if( project.criteria__c == criteria ) {
            retVal.add(project);
        }
    }
    return retVal;
}

Here is some sample data demonstrating the different cases that occur:

email__c    some_date__c    external_id__c    criteria__c
a@bc.de     2012-11-16             1          good
f@gh.ij     2012-08-20             2          good
f@gh.ij     2012-11-11             3          good
k@lm.no     2012-11-13             4          good
k@lm.no     2012-09-14             5          good
p@qr.st     2012-10-02             6          good
p@qr.st     2012-10-02             7          good
u@vw.xy     2012-07-30             8          good
u@vw.xy     2012-11-14             9          bad

If the criteria that we are searching for is “good” there should be 4 records returned. Below is the list of records that should be returned by the function.

email__c    some_date__c    external_id__c    criteria__c
a@bc.de     2012-11-16             1          good 
f@gh.ij     2012-11-11             3          good
k@lm.no     2012-11-13             4          good
p@qr.st     2012-10-02             7          good

Note:

  • The record with the max(some_date__c) for an email address may not have the max(external_id__c)
    • This is why the record with external_id__c = 4 is returned for the email k@lm.no instead of external_id__c = 5
  • A record for an email address may match the criteria but the record with the max(some_date__c) and if there is a tie the max(external_id__c) may not match the criteria
    • This is why there is no record returned for the email u@vw.xy