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
MultimanMultiman 

GROUP and LIMIT in conflict with using variables in query?

I am using this query in an APEX class

 

var domainNameLike = '%mydomain.com';
accounts = [SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE Email LIKE :domainNameLike)]; 

 

For performance reason I want to limit the number of results with a GROUP BY and/or LIMIT statement in the WHERE subquery like this: 

 

accounts = [SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE Email LIKE :domainNameLike GROUP BY AccountId LIMIT 10)]; 

 

However when I am trying to save this, it throws an exception "Error: Compile Error: expecting a right parentheses, found 'GROUP'  "

 

does the variable :domainNameLike somehow conflict with GROUP or LIMIT statements?

What am I doing wrong? How can I limit and group in that subquery?

 

Thanks

Alex

BritishBoyinDCBritishBoyinDC

I'm not sure you can do that in the nested subquery...

 

If you're already querying all the contact rows, you can use a sObject set to give you a unique list, which is probably just as easy...

 

 

String domainName = '%mydomain.com';
 
Set<Account> uniqueaccounts = new Set <Account> ();

for (Contact [] cons: [SELECT AccountId, Account.Name FROM Contact WHERE Email LIKE :domainName] ) {

for (Contact c: cons) {
uniqueaccounts.add(new Account(Id = c.Account.Id, Name = c.Account.Name));
}

} 

system.debug(uniqueaccounts.size());