[SalesForce] How to select the first record from multiple records

I have been able to make the function a bit more efficient and it definitely uses a lot less heap space. Thanks to @eyescream's updated solution for inspiration.

This version uses only 2 loops instead of three. In the first loop I find the "most recent" project that matches the criteria. Then in the second loop I query just for those records that don't match the criteria. If the record that does't match the criteria for an email is more recent then the record that did it is removed from the final result.

This still isn't a very efficient process but I think it might be the best that it gets. However that said if you think you have an improvement I would love to see it.

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

    /* ----- Get "Most Recent" Project that matches criteria ----- */
    for( Project__c project : [ Select email__c, some_date__c, external_id__c, criteria__c 
                                From Project__c 
                                Where criteria__c = :criteria 
                                Order by some_date__c, external_id__c ] ) { 
        emailProjectMap.put(project.email__c, project);
    }

    /* ----- Get Projects that match emails but don't match criteria ----- */
    for( Project__c project : [ Select email__c, some_date__c, external_id__c, criteria__c
                                From Project__c 
                                Where email__c in :emailProjectMap.keySet()
                                And criteria__c != :criteria ] ) {

        /* ----- If project that doesn't match criteria is "More Recent" than   ----- */
        /* ----- project that matches criteria remove matching project from map ----- */
        if( emailProjectMap.containsKey(project.pc_email__c) ) {
            Project__c criteriaProject = emailProjectMap.get(project.pc_email__c);
            if( criteriaProject.Delivered_Date__c < project.Delivered_Date__c || 
                ( matchProject.Delivered_Date__c == project.Delivered_Date__c && 
                  matchProject.project_id__c < project.project_id__c ) ) {
                        emailProjectMap.remove(project.pc_email__c);
    }

    return emailProjectMap.values();
}

You may be able to utilize GROUP BY to do this. Something like:

SELECT
    Master__c, MAX(LastModifiedDate)
FROM
    Child__c
WHERE
    Master__c IN :masterIds
ORDER BY
    Master__c DESC, LastModifiedDate DESC
GROUP BY
    Master__c

This will only return a single record per Master__c object. The only issue you may have is that you need to utilize aggregate functions which may cause you some issues depending on what data you want.

To give an example, I ran this query in my test org. My test org has 3 accounts with BillingState set to CA:

SELECT
    BillingState
FROM 
    Account
WHERE
    BillingState = 'CA'
GROUP BY
    BillingState

When this query is run, it only returns a single record since it grouped the results together into a single record. I did not need to use an aggregate function on BillingState because it was part of the GROUP BY. If it was not part of the GROUP BY, I would need to use an aggregate function on it. However, I wouldn't be able to pull out the Id of the account, because technically it is an aggregate of all 3 accounts. There is no aggregate function to get only a single Id from it.

If you need the Ids for your records, your first approach is best in my opinion.