function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
thunksalotthunksalot 

Getting two IDs with an Aggregate Query

For each Account, I need to know the ID of the Opportunity related to it that has the newest renewal date.  I was thinking that I could query Opportunity records for their ID with a GROUP BY on AccountID and a MAX(Renewal_Date__c), like this:

 

AggregateResult[] groupedResults
= [SELECT ID, AccountID, MAX(Renewal_Date__c)
FROM Opportunity
WHERE AccountID in :acctIDs AND RecordTypeID = '012A0000000qpET'
GROUP BY AccountID];

That results in a query failed error message:

 

MALFORMED_QUERY: Field must be grouped or aggregated: Id

I get why an aggregate query won't allow me to return the Opportunity ID, given that I could have several aggregate functions like AVG() and MIN() in the same query, each returning a value that corresponds to a different Opportunity ID.  But, I don't know what else to do.  

 

What is the correct way to populate the AccountID-OppID relationship map that I need in this scenario?

Best Answer chosen by Admin (Salesforce Developers) 
TheIntegratorTheIntegrator

Sorry, bit of an oversight, should have thought through it! Come to think of it, I don't think it would be possible to get Id's this way, would have to do it through code, something like this

 

String acc='';
List <Opportunity> maxOps = new List<Opportunity>();
Opportunity[] ops
= [Select  Id, AccountId, Renewal_Date__c
FROM Opportunity
WHERE AccountID in :acctIDs AND RecordTypeID = '012A0000000qpET'
   Order By AccountId, Renewal_Date__c DESC];

for(Opportunity o: ops){
    if(o.AccountId !=null && !acc.equals(o.AccountId)){
        acc = o.AccountId;
        maxOps.add(o);
    }
}

 

All Answers

TheIntegratorTheIntegrator

Why don't you add Id in group by, it will return just 1 opportunity record because of the Max date so it won't make any difference and your purpose would be served as well.

 

AggregateResult[] groupedResults
= [SELECT ID, AccountID, MAX(Renewal_Date__c)
FROM Opportunity
WHERE AccountID in :acctIDs AND RecordTypeID = '012A0000000qpET'
GROUP BY AccountID, Id];

 

 

 

thunksalotthunksalot

YES!!  Thank you, thank you!  I didn't realize you could group by more than one field like that.  I knew it had to be something simple!  

 

Thanks again!

thunksalotthunksalot

Unfortunately, that doesn't seem like the solution after all.  When I add the ID field to the GROUP BY, every single Opportunity record gets returned, which I suppose makes sense given that I'm not grouping by the Opportunity record as well.  Any other ideas?

TheIntegratorTheIntegrator

Sorry, bit of an oversight, should have thought through it! Come to think of it, I don't think it would be possible to get Id's this way, would have to do it through code, something like this

 

String acc='';
List <Opportunity> maxOps = new List<Opportunity>();
Opportunity[] ops
= [Select  Id, AccountId, Renewal_Date__c
FROM Opportunity
WHERE AccountID in :acctIDs AND RecordTypeID = '012A0000000qpET'
   Order By AccountId, Renewal_Date__c DESC];

for(Opportunity o: ops){
    if(o.AccountId !=null && !acc.equals(o.AccountId)){
        acc = o.AccountId;
        maxOps.add(o);
    }
}

 

This was selected as the best answer
thunksalotthunksalot

Thanks for putting that issue to rest for me.  I started to do it in code but thought, there must be a way a real SOQL ninja would do this without code!  

 

Your code solution was simpler than mine, so I appreciate your taking the time to write the solution rather than just saying "do it in code".  Thanks so much!