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
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.