Tell me more ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.
up vote 5 down vote favorite

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?

share|improve this question
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 at 16:22
Good try - but won't work unless we know name of opportunity relationship as you say. – dublintech Jan 30 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 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. – Greg Grinberg Jan 30 at 17:15

1 Answer

up vote 3 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.