How to query distnict opportunity records

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


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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);

Method 2

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();

Method 3

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

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x