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?

  • 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 Jun 21 '18 at 23:51
  • Why you are not doing soql on opportunity with order by and use relation to get account fields. – Avijit Chakraborty Jun 22 '18 at 0:40

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

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

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.