Querying only the first record in a relationship where the relationship can have 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();
}

Something like this?

SELECT Name, COUNT(Id)
FROM Search_Term__c
GROUP BY Name
ORDER BY COUNT(Id) DESC
LIMIT 5

(assuming you're storing the searched phrases in Name field)

Here's whole SELECT reference: http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select.htm

You'll get the results as List<AggregateResult> and then you might have to cast them to some helper wrapper class or split into separate list of strings and integers... Something like this

List<Wrapper> results = new List<Wrapper>();
for(AggregateResult ar: [SELECT Name, COUNT(Id) c
FROM Search_Term__c
GROUP BY Name
ORDER BY COUNT(Id) DESC
LIMIT 5]){
    results.add(new Wrapper(String.valueOf(ar.get('Name')), Integer.valueOf(ar.get('c'))));
}

public class Wrapper{
    public String name {get;private set;}
    public Integer value {get;private set;}
    public Wrapper(String n, Integer v){
        name = n;
        value = v;
}