I have to get the top 5 opportunities for all users.

One way to do this is:

for (User user: users) {
    SELECT ID, Amount FROM Opportunity where OwnerID = user.ID order by amount desc limit 5;

However with a lot of users this is going to run into governor limits. Any tips on how I gan get this info for all users in one query.

  • What is the context for this request? Where will you be displaying this information?
    – zachelrath
    Dec 21, 2012 at 11:53
  • @zachelrath I am persisting it and then sending it in an email.
    – dublintech
    Dec 21, 2012 at 12:15
  • Ah, just posted an example for Visualforce --- but you can easily use the same procedure for persisting and sending in an email.
    – zachelrath
    Dec 21, 2012 at 12:40

2 Answers 2


I would use SOQL with a Subquery. However, because there is no relationship syntax for the Opportunity OwnerId to User relationship (you'd expect it to be "Opportunities"), you'll first need to create another Lookup field on the Opportunity object to "mirror" the actual Owner. I'd call it OwnerMirror__c or OwnerDup__c, and I might even put a Lookup filter on it to restrict it to always be the Opportunity's actual Owner. Regardless, you'll need to create a Trigger on Opportunity which automatically sets this OwnerMirror__c to be whatever OwnerId is, at least on insert, and probably before update as well.

duplicate Owner lookup field on Opportunity

Example: a Visualforce page to display top 5 Opportunities for each User

// Apex controller
public with sharing class OppsController {
  public List<User> getOppsByUser() {
    return [
      SELECT Name,
        (select Name, Amount 
        from Opportunities__r
        order by Amount DESC
        limit 5)
      FROM User
      ORDER BY Name

Here's a sample Visualforce page:

<apex:page controller="OppsController" tabStyle="Opportunity">
<apex:pageBlock title="Top 5 Opportunities for each User">
<apex:repeat value="{!oppsByOwner}" var="owner">
    <apex:pageBlockSection title="{!owner.Name}">
    <apex:pageBlockTable value="{!owner.Opportunities__r}" var="opp">
        <apex:column value="{!opp.Name}"/>
        <apex:column value="{!opp.Amount}"/>

Visualforce Page showing top 5 opportunities by Owner

And here's the trigger code for before insert (very obvious but just thought I'd include it):

trigger Opportunity on Opportunity (before insert, before update) {
  // Set the OwnerDup field to the opp's OwnerId.
  // From here on out,
  // a Lookup Filter will force this field
  // to remain equal to the Opp's OwnerId
  for (Opportunity opp : Trigger.new) {
    if (Trigger.isInsert 
    || (Trigger.isUpdate && Trigger.oldMap.get(opp.Id).OwnerId != opp.OwnerId))
    opp.OwnerDup__c = opp.OwnerId;

The Apex code could easily be used in an EmailHandler, Batch Apex, or other Apex context to persist or otherwise make use of this data.

  • Not sure this will work for me but it is as close as I'll get to an answer.
    – dublintech
    Jan 31, 2013 at 9:35

You would need to 'bulkify' your query, instead of querying within the loop, try:

Set<ID> userIds = new Set<ID>();
for( User usr : users )
 userIds.add( usr.Id );

List<Opportunity> opps = [SELECT ID, Amount FROM Opportunity where OwnerID in userIds order by amount desc limit 5;

Of course, if your query returns too many rows, you will still hit a governor limit at which point you may have to consider an alternate approach depending on your use case.

  • I need top 5 opportunities for every user. So if I have 100 users, that's 500 Opportunities I need.
    – dublintech
    Dec 21, 2012 at 11:39
  • I think you would need to retrieve all records and use Apex to, i'll modify the answer to reflect what i mean. Dec 21, 2012 at 12:35
  • Actually, I won't - Zach's sub-query looks like it will give you what you need without any additional Apex to group etc. Dec 21, 2012 at 12:43

You must log in to answer this question.

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