How to retrieve only the highest version number and work with those results

Question

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:

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);
}

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...)