I wish to get the top 5 opportunities for each user.

I do:

SELECT OwnerId, Amount, Probability, ExpectedRevenue
FROM Opportunity 
WHERE isClosed = False 
  AND Opportunity.OwnerID IN :userIDs
  AND ExpectedRevenue > 0 ORDER BY OwnerId, ExpectedRevenue DESC

I iterate over result set and pick out top 5 for each user. Easy. However, the problem is say you have 50 users with 1,001 opps each. You are going to hit the 50,000 governor limit.
I need to restructure this query so that it only returns the top 5 for each user which means it will only return 50 * 5 = 250 records.

I know about the limit keyword but I can't limit this query to 250 because that could just get me 250 opps from the 1st user who has 1,000 opps.

Any tips?

  • 1
    Uuu, tricky! usually a subquery works good for that, sth like SELECT Id, (SELECT Id FROM Opportunities ORDER BY ExpectedRevenue DESC LIMIT 5) FROM User but the User object is special. Relationship between Opportunity and the Owner is not named (comes blank as result of the describe), it's not "Opportunities", not "OwnedOpportunities"...
    – eyescream
    Jan 30, 2013 at 16:22
  • Good try - but won't work unless we know name of opportunity relationship as you say.
    – dublintech
    Jan 30, 2013 at 16:33
  • 1
    Custom lookups to User object DO have relationship names. So you could try creating a hidden Owner__c field, perform initial data load and later keep it in sync with workflow. You should have no problems using it in the query... Sounds a bit hacky though. Or write a trigger that makes the owner an auto member of OpportunityTeam (Role = 'Owner'?), relationship name for that is OpportunityTeams.
    – eyescream
    Jan 30, 2013 at 17:04
  • 1
    Creating this custom lookup is probably the best way but you could also just change your query to just order by expectedrevenue. If your users are all doing opportunities around the same size you should be able to get a list of top 5 for everyone without hitting the query limit. Jan 30, 2013 at 17:15

1 Answer 1


I provided a solution to this as an answer to a similar question: Top 5 Opportunities for each user.

The best way to achieve this is to define a duplicate Owner lookup field on the Opportunity object (kept in sync with true-owner by a trigger, perhaps). This lookup field creates the missing child relationship Opportunities__r needed for a SOQL subquery, which you can have a LIMIT 5 on to just pull in the top 5 Opportunity records for each User.

You must log in to answer this question.

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