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
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);
}
Best Answer
GROUP BY Id
is never what you want. The general point ofGROUP 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.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 theGROUP BY
clause, or be part of an Aggregate Function (AVG()
,MAX()
,MIN()
, etc...)