SOQL query with map of contacts and MAX date

Question

I'm trying to write a query to return the most recent survey completed date for a map of contacts.

Current (unworking) query is:

SELECT Contact__c, MAX(Date_of_Completion__c)myDate, MAX(Score__c)myScore
FROM Survey_Response__c
WHERE Contact__c IN: contactMap.keyset()
GROUP BY Contact__c

The issue here is that I don't actually want the Max Score, I want the score that is attributed to the Max myDate. However, removing the aggregate function from the score means I need to add it to the Group by... and that won't work because it's a non-groupable number field.

Trying to make this bulkified (as its part of a trigger handler). I know how I'd handle this in SQL with Rank... just not sure how to in SOQL.

Answer 1

If you query on Contact instead, you can order the child records and then take the first:

for (Contact parent : [
    SELECT (
        SELECT Score__c FROM Survey_Responses__r
        ORDER BY Date_of_Completion__c DESC NULLS LAST
        LIMIT 1
    )
    FROM Contact WHERE Id IN :contactMap.keySet()
]){
    if (!parent.Survey_Responses__r.isEmpty())
        system.debug('Newest Score: ' + parent.Survey_Responses__r[0].Score__c);
}