How to query distnict opportunity records

Question

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

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

Answer 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)
{
    if(!mapAccountWithOpporty.containsKey(opty.AccountId))
    {
        mapAccountWithOpporty.put(opty.AccountId, opty);
    }
}

Answer 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)
    accountIds.add(record.AccountId);
Integer uniqueAccountIdCount = accountIds.size();

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

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