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
PrazPraz 

select distinct in apex, soql

Hi in absence of select distinct can somebody please send me the alternative logic for select distinct in sfdc please?

 

I tried the following link but this logic is not working

 

http://developinthecloud.wordpress.com/2009/06/28/soql-distinct-keyword/

Best Answer chosen by Admin (Salesforce Developers) 
Shailesh DeshpandeShailesh Deshpande

List<Account> lstAccount = new List<Account>();

 

Set<String> setAccountName = new Set<String>();

 

lstAccount = [select id,Name other fields from account] ;

 

 

for (Integer i = 0; i< lstAccounts.size(); i++)

{

    setAccountName.add(lstAccount[i].Name); // contains distict accounts

}

 

All Answers

Shailesh DeshpandeShailesh Deshpande
use sets...they'll give u distinct values...
PrazPraz
can you please paste a sample query here...
Shailesh DeshpandeShailesh Deshpande

List<Account> lstAccount = new List<Account>();

 

Set<String> setAccountName = new Set<String>();

 

lstAccount = [select id,Name other fields from account] ;

 

 

for (Integer i = 0; i< lstAccounts.size(); i++)

{

    setAccountName.add(lstAccount[i].Name); // contains distict accounts

}

 

This was selected as the best answer
PrazPraz
Tnx man :)
Tom Hoban.ax728Tom Hoban.ax728

The suggested work around solution given isn't practical when working with large data sets.  You'll get a "too many query rows" exception.  (the limit is 10K).  I have a situation where I need to look at 180,000 accounts.  I am trying to troubleshoot a production situation and I would like to find a distinct list of owners in the account table.  So the only thing I can think of is write a batch class to do this.  This uses too much bandwidth of our time for a simple troubleshooting scenario.  Besides that, to get it into production environment, I might need to create unit tests.   I see postings where "select distinct" was asked for back in 2007.  This is a feature that is so basic in every relational database product.  Why can't SF step up and start providing the basic features that are needed for real application development.  This is just one of many features that they are dragging their feet on.
sdetweilsdetweil

ok, but how do you get the distinct records out of the Set?  there is no 'keys',  or set to list function..

 

Sam
sdetweilsdetweil

another missed class library issue..

 

list.add(set)

 

will populate a list object from a set object.

 

sam
SForceBeWithYouSForceBeWithYou

You can try something like this.

 

Set<ID> ownerIDs = new Set<ID>();
for(AggregateResult ar : [SELECT OwnerId FROM Account GROUP BY OwnerId]){
	ownerIDs.add(ar.get('OwnerId'));
}
// Use this to get an SObject unique list
User[] acctOwners = [SELECT Id, Name FROM User WHERE Id IN :ownerIDs];

// You can then use this to get generate a SelectOption unique list
List<SelectOption> selopsAcctOwners = new List<SelectOption>();
for(User u : acctOwners){
	selopsAcctOwners.add(new SelectOption( u.Id, u.Name) );
}

 

 

CAVEAT:  If you have 50 unique account owners for 65,000 accounts, you'll have 50 Aggregations, but 65,000 rows... and error out hitting the governor limit of 50,000 rows.  You can limit this with a WHERE or HAVING clause.  Digging into QueryLocators is not for me yet, and probably not worth the effort for me... yet.
Ryan DRyan D

Sets are unique by definition, if you try to add a duplicate to the set 'nothing' will happen.
SForceBeWithYouSForceBeWithYou
+1 for the non-sequitur ... but nice try!
Ryan DRyan D

That's what I get for not reading the whole thread... ;)

 
SForceBeWithYouSForceBeWithYou

To be fair, you can initialize a Set with a query just like you can with a List or Map, but seeing as people doing a SELECT DINSTINCT(myField) on something are looking for rows for the distinct values (rather than a row for each record), this doesn't help much.  

 

Let's say you want to populate a drop down with current account owners (who are active).

This snippet:

 

Set<Account> setAccounts = new Set<Account>([SELECT OwnerId, Owner.Name FROM Account WHERE Owner.IsActive = true]);

 

Still leaves us needing to cycle through this set of accounts (could be 2000+)in order to build a set just of the owners (which could be 15). 

 

Set<String> setOwners = new Set<String>();
for(Account a : setAccounts){
    setOwners.add(a.Owner.Name);
}

 Something like this could be done with a subselect:

 

Set<User> setOwners = new Set<User>([SELECT Id, Name FROM User WHERE Id IN (SELECT OwnerId FROM Account)]);

 

But row limits would apply, as well as any restrictions on what you can do inside of the sub query.

 

Wanting to do something like this with picklist values or number fields would be harder.

 

PS: If there ever is a SELECT DISTINCT(field) in SOQL, it will most likely return AggregateResults :-)

 
Paul Allsopp 3Paul Allsopp 3
GROUP BY