How to select Unique values in SOQL

Question

I am trying to get unique values by Territory_ID__c with a (Salesforce SQL) SOQL query.

I tried this:

return [select id,name,ANNUAL_CALLS__c,city__c,state__c,No_Of_Targets__c,
Territory_ID__c,Territory__r.name,Territory__r.ANNUAL_CALLS__c 
from zip__c where name in :sArr unique by Territory_ID__c];

But this does not return the unique values. How can I get unique values of a column?

Answer 1

 List<zip__c> lstzips= [select id,name,ANNUAL_CALLS__c,city__c,state__c,No_Of_Targets__c,Territory_ID__c,Territory__r.name,Territory__r.ANNUAL_CALLS__c from zip__c where name in :sArr ];

 Map<String,zip__c> mapStrByzip=new Map<String,zip__c>();

for(zip__c zipone:lstzips){
    mapStrByLstzips.put(zipone.Territory_ID__c,zipone);//This is map and hence it will always have unique values 
}

system.debug('List return'+mapStrByLstzips.values());

Use Maps for this and make Territory_ID__c as key for the Map and that should give you unique values

Answer 2

A SOQL alternative to selecting distinct rows:

SELECT name, COUNT(Id)
FROM Lead
GROUP BY name

Shows you all of the distinct names, with how many of them there are.

One drawback is if there are too many rows in the table, it will just fail, just like sales force is in an existential way.

Answer 3

You don't have the distinct keyword in SOQL I'm afraid - you'll need a coded workaround.

Wes Nolte wrote a blog post explaining how to do this a few years ago:

http://th3silverlining.com/2009/06/28/soql-distinct-keyword/

Backup in case blog goes down:

Apex trick that can be employed as a work-around

public static List<String> getDistinctLastnames(List<String> duplicates){
    List<String> distinctLastnames = new List<String>();
    for(String lastname: duplicates){
        Boolean found = false;
        for(Integer i=0; i< distinctLastnames.size(); i++){
        //Check if current lastname has been added yet
        if(lastname.equalsIgnoreCase(distinctLastnames[i])){ 

            found=true;
            break;
        }
    }
    if(!found)
        distinctLastnames.add(lastname);
    }
    return distinctLastnames;
}

Answer 4

SOQL doesn't support DISTINCT keyword as in SQL

In this case you can use group by as below:

return [select id,name,ANNUAL_CALLS__c,city__c,state__c,No_Of_Targets__c,Territory_ID__c,Territory__r.name,Territory__r.ANNUAL_CALLS__c from zip__c where name in :sArr GROUP BY Territory_ID__c];

The problem with this approach is, governor limits are charged with the number of records processed than the actual records you are returned with. So there is always a problem at the corner.

the best way to do this is add unique values to a list and use in query. For demo see the link suggested by Bob Buzzard in the above answer.

Answer 5

You can use SOQL COUNT_DISTINCT() keyword. This will just count how many of your items values are distinct. This keyword is part of Aggregate functions.

https://developer.salesforce.com/docs/atlas.en-us.230.0.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions_field_types.htm?search_text=aggregate

Answer 6

You can distinct values of any field using the below code:

//get distinct field values
list<AggregateResult > ma = [select tag__c, COUNT(id) FROM Download_LineItem__c GROUP BY tag__c];
set<string> se = new set<String>();
for(AggregateResult m: ma){
  se.add((string)m.get('tag__c'));
}
system.debug('se``` '+se);

Answer 7

In Spring '18, List.contains() was added. That with a SOQL for-loop seems like a nicer approach than what we've had to do to get unique records. Below is a generic example to get all unique primary contacts (from Accounts that have a Primary_Contact__c contact lookup):

List<Id> conIds = new List<Id>();
List<Contact> contacts = new List<Contact>();

// SOQL for-loop Accounts
for(Account acc : [
    SELECT Primary_Contact__r.Id, Primary_Contact__r.LastName
    FROM Account
    WHERE Primary_Contact__r.Id != null
]) {

    // If we have contact already, continue to next loop
    if(conIds.contains(acc.Primary_Contact__r.Id)) {
        continue;
    }
    
    // Keep unique contact
    contacts.add(new Contact(
        Id = acc.Primary_Contact__r.Id,
        LastName = cli.Primary_Contact__r.LastName
    ));
    
    // Add contact to list so it won't be picked up again
    conIds.add(acc.Primary_Contact__r.Id);
}

System.debug(contacts.size() + ' unique contacts');