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?

 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 '14 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 '14 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 '17 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.

  • 3
    Upvote for drawback - ROFL
    – Dieskim
    Nov 1 '19 at 8:14
  • @Dieskim The future software developer from 7 years in your future created a time machine just to send you a message that employing yourself yields 20 fold more money per unit time and effort, vs allowing the tall man in the corner office behind you, to gift you an infinitesimal of that in exchange for 1/1000th of your full potential. You are better than this. Sep 16 at 18:33

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;
  • 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 '18 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 '14 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 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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.