function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SurjenduSurjendu 

How can i get distinct elements from the database

How can i get distinct elements from the database? Does SOQL not provide something like unique or distinct?
Best Answer chosen by Admin (Salesforce Developers) 
apexsutherlandapexsutherland
AcMEGX there is currently no way to do a "Select distinct" in SOQL. I would recommend voting for and posting a comment on the Idea that I linked to earlier in the thread.

All Answers

apexsutherlandapexsutherland
No, SOQL does not yet have a DISTINCT command. Please vote for my idea to have it added:

http://ideas.salesforce.com/article/show/69480/Select_Distinct_Expression_in_SOQL
http://ideas.salesforce.com/article/show/69480/Select_Distinct_Expression_in_SOQL


Message Edited by apexsutherland on 02-18-2008 08:13 PM
SurjenduSurjendu
Thats crap!!! It makes sense to make one field unique but what about composite primary keys...So I have to basically weed out the duplicate stuff in controller..hmmmm...they should have thought about Distinct..Infact their SOQL needs to be more thorough
EnderEnder
All SObjects are distinct.  What gave you the impression that they aren't? 

I think its helpful to think about the SObjects from an Object-Oriented view rather than a strict relational database sense.

Every SObject has an id and every id is unique.   While there are edge cases for a distinct application they are in general very edge and can usually be rectified by shifting your perspective to what you are actually querying.
SurjenduSurjendu
Creating an object with an unique Id does not make it distinct. If that would have been the case none of the database vendors would have provided the concept. I agree with you that we have to look sObjects as objects from OO Design and not tables/rows from a strict RDBS persepective. But eventually they get stored in the Database and when u want to query an object which is a combination of 3 primary keys u just cannot. Also while creating an object SF does not provide u the option of creating a composite PK which engulps 2 or 3 fields. In that case all we are left out iwith no other option but to weed out the duplicates in your code. That aint good...

Dont get me wrong buddy..Thats a requirement in our app and as this is a place to exchange ideas and stuff i thought of doing the same...At retrospect i should have not started my post with "crap"...My apologies...
EnderEnder
Hey Buddy,

" Creating an object with an unique Id does not make it distinct" -- It does not make the other fields on an object distinct, but it definitely makes the id field distinct, and since the id field is the primary key, the object itself distinct.  OO, not relational.

Salesforce provides each SObject you create with a unique primary key.  This is the id field.  If you wanted to store the object in your own database, why wouldn't you already use the unique identifier provided?

"when u want to query an object which is a combination of 3 primary keys u just cannot"  --- if they are primary keys, then they are unique. 

"object SF does not provide u the option of creating a composite PK which engulps 2 or 3 fields"  -- it is fairly trivial to create a unique custom field that is populated either through a trigger, s-control or formula field that is exactly that.  However, why would you create such a field when a valid primary key is already provided for you?

The problem you are describing still does not seem to need the distinct keyword. 

Can you post some of your apex code logic that you need to use to de-dupe?  Maybe that would shed some light on the problem at hand.



.
apexsutherlandapexsutherland
Ender, you're not considering the records in their REALTIONSHIPS to one another - sure, Salesforce isn't a full RDBMS, but it is still a relational data store - distinct object IDs can still show up multiple times in the foreign key field (Lookup or Master-Detail relationship) of another object, either in a one-to-many or many-to-many join. It would be immensely helpful to be able to do a "Select DISTINCT ContactID From Case Where Priority  = 'High'", or something like that. The fact that the records stored in an object always have unique keys within that same object isn't relevant to this discussion.

Can you show us how to get the results that we want using straight SOQL, without the ability to use a DISTINCT command?
EnderEnder
That is a case where Distinct would be applicable, but that was not the business case that Surjendu was describing. 

Distinct isn't necessary in your case but yes, absolutely helpful and warranted.

Here it is without distinct using sql syntax

"Select contactid from contact where contactid in (select contactid from case where priority = 'high')"
AcMEGXAcMEGX

Hi Guys,

 

I think this thread is old however I have a concern that is similar to the subject of this thread...

 

Quick question: how do i do this in SOQL without writing a loop iterating over the results?

 

in SQL: Select distinct name__c from CustomObject__c

in SOQL: ? 

Message Edited by AcMEGX on 06-02-2009 10:57 PM
apexsutherlandapexsutherland
AcMEGX there is currently no way to do a "Select distinct" in SOQL. I would recommend voting for and posting a comment on the Idea that I linked to earlier in the thread.
This was selected as the best answer
d3developerd3developer

I wrote up a work around for this problem here:

 

http://d3developer.wordpress.com/2009/05/21/workaround-for-select-distinct-in-soql/
AcMEGXAcMEGX
Done and done
hamayoun65hamayoun65
You rock Ender!  That is soooooo cool!
RRC007RRC007

Hi. new to Apex. I tried your code and got an error on

 

{ uniqueCustomObjectSet.add(sl.lunchChoice__c);}


List<String> uniqueCustomObjectList = new List<String>(uniqueCustomObjectSet)

 

Do I have to define uniqueCustomObject Set ??? error says undefined

Thanks
AcMEGXAcMEGX
RRC007 wrote:

Hi. new to Apex. I tried your code and got an error on

 

{ uniqueCustomObjectSet.add(sl.lunchChoice__c);}


List<String> uniqueCustomObjectList = new List<String>(uniqueCustomObjectSet)

 

Do I have to define uniqueCustomObject Set ??? error says undefined

Thanks

Hi,
 
Declare uniqueCustomObjectSet somewhere on the top:    
Set<String> uniqueCustomObjectSet = new Set<String>();
 
Regards,
AcMEGX 
 


RRC007RRC007
Works Great!!! Thank you. Final question...if I wanted to sort the results, how would I code that?


Nick34536345Nick34536345
just call sort() on the List


Paul Allsopp 3Paul Allsopp 3
GROUP BY