I have a master-detail relation ship where I am updating the parent (lets call it Parent__c
) based on its child records aggregated values
(lets call the child, Child__c
).
The child has a relationship to another custom obj, lets call this one LookupObj__c
, in it a picklist, lets call it Category__c
.
I need a query that will determine the 2 most frequent LookupObj__c
picklist values based on the relationship with Child__c
. For this I came up with an
aggregate query that groups on Parent
, and on Category
then order on the count desc so that I get the highest 2 per parent first on the results
and where there is even results (lets say a match on category counts, then it is determined on category text comparison (so A_category would come before B_category)
So far I have something decent built where I get the results order the way I want BUT I am unsure how to get ONLY the first 2 most frequent LookupObj__c
values
for each parent and set that on the parent
Code:
for(AggregateResult ar: [SELECT count(id) cnt, LookupObj__r.Category__c cat, Parent__c Parent,
Parent__r.Primary_Category__c,Parent__r.Secondary_Category__c
FROM Child__c
WHERE Parent__c IN :ParentsToUpdateSet
GROUP BY Parent__c, LookupObj__r.Category__c ,
Parent__r.Primary_Category__c,Parent__r.Secondary_Category__c
order by Parent__c, count(id) desc, LookupObj__r.Category__c asc
]){
system.debug('aggregateResult: ' + ar);
Parent__c Parent = new Parent__c();
Parent.Id = (id)ar.get('Parent');
String cat = (String)ar.get('cat');
//HOW DO I GET THE 2 MOST FREQUENT CATEGORIES PER TRIP ASSIGNED???
parent.primary_category__c =
parent.seconday_Category__c =
}