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

Improve this question
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
    Commented 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
    Commented Nov 17, 2015 at 18:53

1 Answer 1

Reset to default
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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .