We have an object called "Compensation__c". This object contains the compensation a partner receives for selling a "Product2".

"Compensation__c" has a start date. Before this date this compensation is not valid yet. A "Product2" can have multiple compensation records scheduled in the future, so today I will get €1,- and I might get €2,- tomorrow for this same product and €1,50 next week.

I need to create a SOQL query which returns the current compensation record for a selection of products.

SELECT Id, Product__c, PartnerCompensation__c FROM Compensation__c WHERE CompensationType__c = :compensation AND StartDate__c <= TODAY AND Product__c IN :productIds

How do I select the most recent active compensation? I want to group by Product__c, but then all other fields must be aggregated as well. I do not want an average or max compensation, I want the actual compensation...

1 Answer 1


You can use a parent-child subquery (a.k.a. a Left Outer Join) to associate Compensation__c records to their related Product2 record.

This will allow you to filter Compensation__c records as you wish, using ORDER BY and LIMIT to grab just the most recent, appropriate record.

    (SELECT Id, PartnerCompensation__c 
        FROM Compensations__r 
        WHERE CompensationType__c = :compensation AND StartDate__c <= TODAY 
        ORDER BY StartDate__c DESC 
        LIMIT 1) 
 FROM Product2 
 WHERE Id IN :productIds]

Compensations__r is the child relationship name (which you should be able to see/verify by looking at the definition of your Product__c lookup field).

A subquery returns a List<SObject> embedded in the resulting Product2 records, and you'd access the Compensation__c records like this

for(Product2 prod :<the query>){
    // Subqueries result in fully-fledged SObject records
    // Once you pull a record out of the list, you can use it just like you would
    //   any other SObject.
    Compensation__c mostRecent = prod.Compensations__r[0];


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .