0

I want to sort Accounts using the field in Opportunity in my SOQL.

This is the query which selects Accounts and Opportunities. I want to order the Accounts using MAX of Opportunity.ExpectedRevenue field.

Select Id, Name (Select Id, ExpectedRevenue from Opportunities) from Account where Id in :listVar and ownerId = :currentUser

Is it even possible?

2
  • You realize there will potentially be multiple values? Do you want to sort by MAX/MIN/AVG? Can you please edit your post to clarify expected behavior?
    – Adrian Larson
    Commented Jun 21, 2018 at 23:51
  • Why you are not doing soql on opportunity with order by and use relation to get account fields. Commented Jun 22, 2018 at 0:40

2 Answers 2

2

I suggest you just query on the Opportunity object instead. You can use aggregate queries if you want just one record per parent. That would look like:

SELECT Account.Name, MAX(ExpectedRevenue) maxRevenue
FROM Opportunity
WHERE AccountId in :listVar
AND Account.OwnerId = :currentUser
GROUP BY  Account.Name
0

You will need to come at it from the other direction.

SELECT Id, ExpectedRevenue, Account.Id, Account.Name 
    FROM Opportunity where Account.Id in :listVar and Account.OwnerId = :currentUser 
    order by ExpectedRevenue DESC

   

You must log in to answer this question.

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