I have a custom object called Asset__c in which there is a lookup field called Site__c which is linked to another custom object called Site__c.

This Assets object has more that 20k records. What i want is Set of string values which has unique site names. I tried grouped query but it didn't work and there was a limit issue.

What i did is, i queried all the records and looped through them and added the Site__r.Name field to a new set. Similar to Site__r.Name i also want to get the set of unique values for few other fields as well.

Is there a more efficient way to do it. please help. below is the sample code.

Set<String> sitelist = new Set<String>();
Set<String> customerlist = new Set<String>();
for(Asset__c ast:[Select Id,Site__r.name, customer_Name__c from Asset__c]) {

Actual query used

SELECT Dealer__r.Name FROM Asset__c WHERE ACM_Service_Level__c IN ('Service Level 2','Service Level 1') AND ECM_Enabled__c = true AND RecordType.Name = 'Machine' group by Dealer__r.Name
  • 20000 recs should be well within Aggregate query capabilities; – cropredy Apr 29 at 23:21
  • Do you actually need the other fields in the query or can you just use: SELECT Site__r.name FROM Asset__c GROUP BY Site__r.name – Brandon Wind Apr 29 at 23:26
  • @BrandonWind I need other fields as well. – Sisir Apr 29 at 23:45
  • @cropredy i got an error with aggregate query. System.QueryException: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch – Sisir Apr 29 at 23:48
  • Exactly how many Asset__c? And use edit to show exact Aggregate query – cropredy Apr 29 at 23:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.