How to update only one latest child record's from list of Parent records ?

 This post is all about how to use aggregate query effectively or write less complex apex code.

Use Case: Lets say you need to update latest case record of each opportunity and  you have been given list of opportunity Ids. One opportunity can have multiple cases. How can you do it?

Approach - 1 If you are strong in SQL, you might be linking to use Group By Opportunity__c Order By CreatedDate Desc and Limit 1. SOQL does not support Order By limit along with Group By.

Approach - 2 Using Map to hold Opportunity Id with latest Case

Here is sample code 

// List of Opportunity IDs

List<Id> opportunityIds = new List<Id>{'oppId1', 'oppId2', 'oppId3'};

// Map to hold the latest Case for each Opportunity

Map<Id, Case> latestCaseMap = new Map<Id, Case>();

// Query all cases related to the Opportunity IDs,

sorted by CreatedDate descending to get the latest case first

List<Case> cases = [

    SELECT Id, Opportunity__c, Subject, Status, CreatedDate

    FROM Case

    WHERE Opportunity__c IN :opportunityIds

    ORDER BY CreatedDate DESC

];

// Loop through the cases and store the latest case for each Opportunity ID

for (Case c : cases) {

    Id oppId = c.Opportunity__c; // Assuming 'Opportunity__c'

is the lookup field on the Case object

   

    // If this Opportunity ID is not already in the map, add the current case

as the latest case

    if (!latestCaseMap.containsKey(oppId)) {

        latestCaseMap.put(oppId, c);

    }

    // Since the cases are already sorted by CreatedDate DESC, the first case

encountered is the latest one,

    // so no need to process further cases for the same Opportunity.

}

// Now latestCaseMap contains each Opportunity ID and its corresponding latest Case.

// You can iterate over it and perform the necessary update for each latest Case

List<Case> casesToUpdate = new List<Case>();

for (Id oppId : latestCaseMap.keySet()) {

    Case latestCase = latestCaseMap.get(oppId);    

    // Perform any necessary updates on the latest case

    latestCase.Status = 'Closed'; // Example: updating the status to 'Closed'

   

    // Add the case to the list for DML update

    casesToUpdate.add(latestCase);

}

// Update the latest cases for each Opportunity

if (!casesToUpdate.isEmpty()) {

    update casesToUpdate;

}

System.debug('Updated the latest case for each Opportunity.');

We can accomplish requirement in approach -2, but this is not efficient one.

Approach - 3 Using Aggregate Query.

Set<Id> caseIds = new Set<Id>();

List<Case> caseUpdateList = new List<Case>();

Case caseTemp;

// Query to get one Case ID per Approved_Lead__c (Opportunity)

List<AggregateResult> caseList = [

    SELECT Approved_Lead__c, Max(Id) caseId

    FROM Case

    WHERE Approved_Lead__c IN :oppyIds

    GROUP BY Approved_Lead__c

];

// Iterate through the results

for (AggregateResult ar : caseList) {

    Id opportunityId = (Id) ar.get('Approved_Lead__c'); // Opportunity ID (Approved_Lead__c)

    Id caseId = (Id) ar.get('caseId'); // The Case ID (MIN or MAX)

    caseIds.add(caseId);

    // Now you have the Opportunity ID and the Case ID

    System.debug('Opportunity ID: ' + opportunityId + ' Latest Case ID: ' + caseId);

}

for(Id cseId: caseIds){

caseTemp = new Case(Id=cseId, Status='Closed');

caseUpdateList.add(caseTemp);

}

If(!caseUpdateList.IsEmpty()){

    Update caseUpdateList;

}