I wish to get the top 5 opportunities for each user.
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
limitkeyword 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.
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.