Update Only the Latest Child Record for Each Parent (Apex & SOQL Tips)

Posted by Vinay Vernekar 14th September 2025

Use SOQL aggregate queries to pick a single latest child record per parent instead of querying all children — more efficient and easier on heap limits. This post compares a Map-based approach and a performant AggregateResult approach with examples and best practices.

Problem overview

Given a list of parent record Ids (for example Opportunity Ids), you need to update only the most recent child (for example the latest Case) for each parent. One parent can have many children, so naïvely querying all children and scanning them in Apex can be inefficient.

Approaches

Approach 1 — Map + sorting in Apex (simple but heavier)

This approach queries all child records for the parent Ids, sorts by CreatedDate desc, and uses a Map to pick the first child per parent. It works but transfers all matching children into memory and can be slow for large datasets.

// Query all cases related to the Opportunity IDs, sorted by CreatedDate descending
List cases = [
    SELECT Id, Opportunity__c, Subject, Status, CreatedDate
    FROM Case
    WHERE Opportunity__c IN :opportunityIds
    ORDER BY CreatedDate DESC
];

Map latestCaseMap = new Map();
for (Case c : cases) {
    Id oppId = c.Opportunity__c;
    if (!latestCaseMap.containsKey(oppId)) {
        latestCaseMap.put(oppId, c);
    }
}

// Build update list
List casesToUpdate = new List();
for (Case latestCase : latestCaseMap.values()) {
    latestCase.Status = 'Closed';
    casesToUpdate.add(latestCase);
}
update casesToUpdate;

Approach 2 — Aggregate SOQL (efficient)

Use a GROUP BY on the parent lookup and select MAX(Id) (or MIN(CreatedDate) as appropriate). MAX(Id) typically returns the latest record when Ids arecreasing, but be careful — if you need true latest by CreatedDate, aggregate MAX(CreatedDate) then join back. The benefit: SOQL returns exactly one row per parent and you only query the child records you need.

// Example: get one Case Id per Opportunity using AggregateResult
Set caseIds = new Set();
List caseList = [
    SELECT Approved_Lead__c, MAX(Id) caseId
    FROM Case
    WHERE Approved_Lead__c IN :oppyIds
    GROUP BY Approved_Lead__c
];

for (AggregateResult ar : caseList) {
    Id caseId = (Id) ar.get('caseId');
    caseIds.add(caseId);
}

List caseUpdateList = new List();
for (Id cId : caseIds) {
    caseUpdateList.add(new Case(Id = cId, Status = 'Closed'));
}
if (!caseUpdateList.isEmpty()) update caseUpdateList;

Notes on correctness

  • If you must guarantee the latest by CreatedDate, use MAX(CreatedDate) and then find corresponding Id(s) in a second query or use a subquery join pattern.
  • MAX(Id) often maps to latest insert, but Id ordering is not a robust substitute for CreatedDate in all cases (especially when data migration or external systems insert records).

Performance and best practices

  • Prefer aggregate queries when you only need one child per parent — reduces rows returned and CPU/heap usage.
  • Always bulkify: accept lists or sets of parent Ids and process in bulk.
  • Limit fields returned to what’s needed (Id and lookup fields to join back or fields you update).
  • Respect FLS and sharing where appropriate.
  • Write test methods that create multiple children per parent to validate that only the intended child is updated.

When to use which approach

Use the Map + sorting approach for small datasets or when you already need many child fields in memory. Use the Aggregate approach when you just need one child Id per parent and want to minimize query results and heap usage.

Conclusion — Why this matters

Choosing the aggregate approach can dramatically reduce the number of rows transferred and processed, lower heap usage, and improve execution speed — especially important in bulk operations, scheduled jobs, or triggers that run over many parent records. For Salesforce admins and developers, this means more reliable automations and better performance; for business users it translates to faster updates and fewer governance issues.

Tags: