1

i want to query the opportunities for a using a set of account ids and get the opportunity for each account that has the most recent start_date__c. so im grouping the opportunity query on account and have max date in my field list, but it doesnt want to return the ID of that record. im having trouble moving it to having or some other means, so im not sure how to write this query. what i THINK should work is

SELECT max(start_date__c), id FROM Opportunity WHERE StageName = 'Signed' AND AccountId IN ('001G000001cK7P2') GROUP BY AccountId

but i have to use an aggregate function around id, so mixed results occur

2
  • you need to include id in your SOQL group by clause. so it will be SELECT max(start_date__c), id FROM Opportunity WHERE StageName = 'Signed' AND AccountId IN ('001G000001cK7P2') GROUP BY AccountId,id
    – Himanshu
    Nov 17, 2015 at 18:49
  • that does not work as it includes the id in the aggregate, thereby bringing back each individual record, not just the one with the max date
    – textual
    Nov 17, 2015 at 18:53

1 Answer 1

1

Well, I am not too good in Aggregate functions. But I have an alternative. You can write a nested query like this.

[Select Id, (Select Id from Opportunities WHERE StageName = 'Signed' order by start_date__c DESC LIMIT 1) from Account where Id IN ('001G000001cK7P2')]

The inner query will make sure you have the max Start date among the opportunities.

2
  • YES! I thought it was a subquery but wasnt able to get it working correctly. Many thanks
    – textual
    Nov 17, 2015 at 18:57
  • Glad I could help! Nov 17, 2015 at 19:01

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.