[SOLVED] Getting the latest entry for every client in SOQL

Getting the latest entry for every client in SOQL

I am trying to write a SOQL query to get the latest entry for every client from a custom object.

The closest I have been able to find is this (from SQL, not SOQL). Do you know if this is possible in SOQL?

SELECT Id,
       Score__c,
       Client__c,
       Date__c
FROM my_custom_object__c mco1
WHERE CreatedDate = (SELECT MAX(mco1.CreatedDate) FROM my_custom_object__c mco2 where mco1.id = mco2.id)

It is of course possible I am completely barking up the wrong tree. Any help would be greatly appreciated.

Solution

Something along these lines

Select id, name, (Select score__c, date__c from my_custom_objects__r order by date__c desc limit 1) From Client__c Where id in (select client__c from my_custom_object__c)

You will need to know what's the "relationship name" (go to your custom objects's definition, to the client lookup and check) to properly write that "my_custom_objects__r".