8

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?

5
 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

| improve this answer | |
  • 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 – Mohith Shrivastava 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
6

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;
}
| improve this answer | |
  • 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
5

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.

| improve this answer | |
1

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.

| improve this 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

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.