Using limit in SOQL subselect.

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?

Answer

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.

Attribution
Source : Link , Question Author : dublintech , Answer Author : Community

Leave a Comment