+ Start a Discussion
GhilliGhilli 

Distint value by SOQL

Hi All,
 
How to get the distinv values using SOQL.
 
In SQL we use DISTINCT keyword.
 
How about in SOQL.
 
Kindly tell me since its delaying my work.
 
Thanks.
 
lamayclamayc
Sorry, can't do. Just like you can't do aggregate functions either like group by
 
 
specifically
 

How is SOQL different from SQL?

SOQL (Salesforce Object Query Language) is similar in syntax to SQL (Structured Query Language), but the two languages also differ in some significant ways.

  • SOQL is only used to retrieve sets of data; you can insert, update, delete and upsert data based on a retrieved SOQL set. SQL can directly retrieve and modify sets of data.
  • SOQL supports extended dot notation to retrieve data from related Salesforce objects (which are similar to tables) and can only link objects which have a pre-existing relationship defined; SQL uses explicit joins to access more that one table.
  • SOQL does not support the full set of SQL syntax. For instance, SOQL does not support the DISTINCT keyword.
  • You cannot use Data Definition Language (DDL) to create custom objects. With the Summer 07 release, you can use the meta-data API in Developer Edition accounts to create custom objects procedurally.
  • SOQL retrieves sets of data as objects, which can call for different design and implementation considerations. You could achieve the same result as DISTINCT for a single value by making that value part of a lookup object.
GhilliGhilli
Hi Lamayc,
 
Thank you very much for your instance reply.
 
I have gone through the link. I have find many useful informations there.
 
But I can not still understand about lookup object you said.
 
If you have any code to get the distinct values kindly share with me.
 
Thank you once again.
 
lamayclamayc
I am not extactly sure what they mean here. I assume they mean you should take all the distinct values and put them in another table and then just have a the table you are using link to the distinct value table (like a foreign key type arrangement)
 
For example
 
Instead of table A having something like
name address
A        ZZZ
B       CCC
A       DDD
 
where you want to find a distinct name (A or B)
 
Break it into 2 table
table A                     table B
link          address
link to A   ZZZ          A
link to B   CCC       B
link to A   DDD
 
so the distinct values are all in B
 
That is my guess what they mean.?   Personally, what I have been doing is just getting all the data and then put it into a set. The set will only have distinct values.
 
Ex:
Set<String> mySet = new mySet<String>();
for (Info__c tmp : [select name from UselessTable__c])
  mySet.put(name);
 
mySet will only contain unique names
 
For multiple distincts, just create multiple sets.
 
It's kind of a hack, and very inefficient (especially since the db could do this much more efficiently, and it would be a lot faster since the db would not have to return all the rows), but it gets the job done.
 
I am not aware of any other way to do it though.
 
 
GhilliGhilli
Hi Lamyac,
 
Thanks for your wonderful reply. It really helped me a lot. 
 
Actually i used the following one to get the unique values. Here i can not use the Set for selectOption. Hence i used map.
 
Code:
public List<SelectOption> getThirdList() {
      Map<String,selectOption> third1 = new Map<String,selectOption>();
      List<SelectOption> third = new List<SelectOption>();        
        for (Activity1__c b : [select Third__c from Activity1__c b where b.Second__c = :secondsel]){
          third1.put(b.Third__c,new SelectOption(b.Third__C,b.Third__C));
        }
      third = third1.values();
      return third;
    }

 
Its working fine now.
 
Thank you once again.