I need to retrieve only the highest version number from a table. Then I need to read each rows results. How do I accomplish this using aggregate results? I only want to retrieve the rows in yellow and loop through the results

Sample Data: enter image description here

I tried this query and it fails. Everything I try seems to now work. Please help

   list<aggregateresult> maxVersion = [select id, name, MAX(versionnumber) vernum, versionmaster from sobject group by id];

for(aggregateresult ar : maxVersion) {
  sobject r = ar;
  system.debug(logginglevel.info, 'My Debug: name ' + r.name);
  • I only want to retrieve the rows in yellow and then loop through the results
    – Ohmicron
    Apr 25, 2022 at 15:33

1 Answer 1


GROUP BY Id is never what you want. The general point of GROUP BY is for there to be multiple (>= 2) records with the same value for the grouped field(s). The record Id is always going to be unique, so nothing will be grouped.

You can group by relationship fields (which are stored as the parent record's Id), it's just grouping by the Id field of the SObject you're working on that makes no sense.

Based on what you've shown us so far, you'd want to GROUP BY Name, to find the highest version number for each process. You access data in an AggregateResult by using the .get() method.

for(aggregateresult ar : maxVersion) {
    // .get() always returns an Object, which then needs to
    //   be explicitly typecast if you want to do much of anything with
    //   it.
    String theName = (String)ar.get('name');
    system.debug(logginglevel.info, 'My Debug: name ' + theName);

Unfortunately, that's about as far as you can go with AggregateResult. Because it's causing (potentially) many records to be grouped together, you can't access the other fields (like Id or versionMaster). Every field that appears in the SELECT clause must also appear in the GROUP BY clause, or be part of an Aggregate Function (AVG(), MAX(), MIN(), etc...)

  • So it's impossible to pull just the yellow rows in one SOQL query because I will not be able to get the data from the other fields? What I'm really looking for is the id of the record that has the maximum version number for the common version master
    – Ohmicron
    Apr 25, 2022 at 16:28
  • 1
    @Ohmicron It's impossible to do with a single query that uses GROUP BY, for reasons I covered in my answer. It would be possible in a single query if each "process" was related to a different parent record (because you could use a subquery to get the child records, and use ORDER BY and LIMIT to retrieve only the child with the highest version number). You may have to resign yourself to querying more rows than you strictly want, and then processing them with Apex to get your desired result.
    – Derek F
    Apr 26, 2022 at 14:16
  • Thanks for the information @Derek F. Appreciate your feedback.
    – Ohmicron
    Apr 27, 2022 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.