I need to display related opportunities in lead page with some requirements like order by unit potential and display only highest unit potential record of the opportunity.means I want to display distinct opportunity records

So I tried to use Count_Distinct but I am not able to parse query.

How can I modify the below query to get the data I want?

Select Id,Number_of_Units__c,Name,StageName,Type,Billing_City__c,Opp_Sic_Code__c from 
Opportunity where Billing_City__c=:cityset AND Opp_Sic_Code__c=:Sicset AND 
StageName='Closed Won' ORDER BY Number_of_Units__c Desc Limit 10

Opportunity Map

I need to display only two opportunities in above scenario first one and third one,opportunity test-- & Internal.Because test-- has the highest unit potential for the account Sample and internal is related to other account

  • can you provide an example with data what you are looking for Commented Jun 6, 2017 at 16:48
  • Do you want to get the opportunity with highest unit potential related to an account?
    – SfdcBat
    Commented Jun 6, 2017 at 18:35
  • Yes @SfdcBat but I need to display only one opportunity from an account.
    – Pavan tej
    Commented Jun 6, 2017 at 18:37
  • @SantanuBoral My question has been updated please check once.
    – Pavan tej
    Commented Jun 7, 2017 at 16:09
  • check my answer, hopefully it will work for you Commented Jun 7, 2017 at 16:37

3 Answers 3


There will not be direct SOQL query which can meet your requirement.

Here is the approach.

Put ORDER BY AccountId,Number_of_Units__c Desc in SOQL

  • Since you are retrieving records in DESC, so highest value for an Account will be displayed first and that will be added to the map.

  • Now, next time, for the same account, if Opportunity record is coming then that will be ignored.

  • finally take the mapAccountWithOpporty.values() which will have Opportunity with highest Number_of_Units__c, perform whatever you want to do.
//this map holds AccountId with that Opportunity which has highest Number_of_Units__c
Map<Id, Opportunity> mapAccountWithOpporty = new Map<Id, Opportunity>();

List<Opportunity> lstOppty = [Select Id, AccountId, Number_of_Units__c,Name,StageName,Type,Billing_City__c,Opp_Sic_Code__c 
                                from Opportunity 
                                where Billing_City__c=:cityset 
                                AND Opp_Sic_Code__c=:Sicset 
                                AND StageName='Closed Won' 
                                ORDER BY AccountId,Number_of_Units__c Desc];
for(Opportunity opty:lstOppty)
        mapAccountWithOpporty.put(opty.AccountId, opty);
  • Thanks. Order By is a great idea. May I ask if there is any reason for using Limit 10?
    – SfdcBat
    Commented Jun 7, 2017 at 17:16
  • 1
    Limit is not required, I have used your limit clause, I have removed at my updated code Commented Jun 7, 2017 at 17:20
  • 2
    Gotcha. Hope the OP finds this helpful to solve his problem.
    – SfdcBat
    Commented Jun 7, 2017 at 17:21

If you want to return all of the fields you have in your query, you likely want to avoid aggregate queries, which is the only place you can use the COUNT_DISTINCT function. Any such attempt would yield:

Field must be grouped or aggregated: ...

You can use post-processing to get the count of distinct AccountId values using a Set. Don't forget to add this field to your query!

List<Opportunity> records = [/*query*/];
Set<Id> accountIds = new Set<Id>();
for (Opportunity record : records)
Integer uniqueAccountIdCount = accountIds.size();

You can do it by two ways.

1) Apex : Programmatic way, as suggested by Adrian Larson.

2) Query using GROUP BY aggregate function. I have shown like below, you need to add all your custom object shown in your posts.

enter image description here

Possible duplicates of the How to select Unique values in SOQL . How to get unique values of two fields in one SOQL?.

  • 1
    My understanding is - OP wants to get the Id of Opportunity which has greatest value in Number_of_Units__c field. Example if an Account A1 has O1,O2,O3 as its opportunity. OP wants to get the Opportunity that has highest value in Number_of_Units__c for A1. Similarly for each account.
    – SfdcBat
    Commented Jun 6, 2017 at 22:04
  • yes and finally i need to display order by Number_of_Units__c will you please guide me @SfdcBat
    – Pavan tej
    Commented Jun 7, 2017 at 8:21
  • @Pavantej Please check Santanu's anwer. Hope it gives you the solution
    – SfdcBat
    Commented Jun 7, 2017 at 17:22

You must log in to answer this question.

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