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


7 Answers 7

 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

  • It'll keep overwriting the values, maybe Map<String, List<Zip__c> would be better/safer.
    – eyescream
    Jan 9, 2014 at 12:30
  • good point it depends what user wants exactly ,if he prefers to segregate as Map<String,List<Zip__c>> its better to handle that way.Thanks Jan 9, 2014 at 12:43
  • Your solution is nice but i dont how it will be usefull to my solution Will you please look in to this @MohithShrivastava salesforce.stackexchange.com/questions/177663/…
    – Pavan tej
    Jun 7, 2017 at 10:31

A SOQL alternative to selecting distinct rows:


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.

  • 4
    Upvote for drawback - ROFL
    – Dieskim
    Nov 1, 2019 at 8:14

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:


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

    return distinctLastnames;
  • 1
    Using a set rather than a list will be more efficient from the CPU governor limit perspective. The addition of an item to a set is always going to be a single "system-space" method invocation, rather than a sequence of "user-space" operations (iteration, comparison and final insertion into a list).
    – Phil W
    Apr 7, 2018 at 10:22

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.

  • 2
    Columns that aren't used in GROUP BY would have to be surrounded with some aggregate function - COUNT, SUM, MAX though. Or you could list more items in GROUP BY - but some stuff like formula fields can't be used that way...
    – eyescream
    Jan 9, 2014 at 12:32

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.


  • This function is useful to know about, but doesn't fit the OP requirement as they wanted to know the actual values, not how many of them there are.
    – Adrian Larson
    Feb 22, 2021 at 14:59

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){
system.debug('se``` '+se);

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)) {
    // 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

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

You must log in to answer this question.

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