+ Start a Discussion
MrTheTylerMrTheTyler 

SELECT DISTINCT equivalent in SOQL?

Let's say I got 50,000 records in a table where there is a field named OwnerID.  Let's say I just want to grab a set of the distinct OwnerIDs and not be charged 50,000 query rows for my governor limits.  What ya'll have to say about 'dat?

 

 

~Tyler

Ritesh AswaneyRitesh Aswaney

Reckon you could use the GROUP BY aggregate function (there is select count_distinct(ownerid) from lead - gives u unique count)

 

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource
MiddhaMiddha

I guess Aggregate queries eat the governor limits based on the number of records you are processing. They only help you save logic but not the governor limits.

 

Do check this, if it really happens.

Imran MohammedImran Mohammed

I do agree as AggregateResult query adds up to the query rows in the governor limits.

I dont see a direct approach to the query you have.

Only post processing after the query will help you in acheiving what you are looking for.

MrTheTylerMrTheTyler

Ritesh,

 

  If I use GROUP BY on the example table I proposed in my first message which contains 50,000 rows but only 10 unique OwnerIDs, the aggregate result may only return 10 aggregate result objects but I will be charged for 50,000 rows on my queryresult and thus exceed my governor limits.

 

 

Cheers,

 

Tyler

MrTheTylerMrTheTyler

It does happen.  :smileywink:

MrTheTylerMrTheTyler

Imran,

 

 If post processing is the only way, then what do I do with a table of 100,000 rows?  How would I process this without hitting governor limits?

 

 

Cheers,

 

Tyler

Ritesh AswaneyRitesh Aswaney

BatchApex ? Cant remember the exact limit, but its some million rows.

 

Also, salesforce recommend use of the 

for(List<sObject> var : [Select ......

 

syntax - as it does some kinda optimisation, and lets you trawl through lists of sobjects, rather than sObjects (benefit is a multiple of the max size of the list)

Imran MohammedImran Mohammed

Using Batch Apex upto 50 million records can be processed.

You can read more on than that in Apex documentaion.

Kamal Kishore SinghKamal Kishore Singh
Here what I did
  • Used data loader to export the data is CSV
  • Used remove duplicate in excel
If you like this please press like button
Jaap ScheperJaap Scheper
Since Summer '18:
Previously, each individual record matched by the SOQL COUNT() and COUNT(fieldName) functions counted toward the query row limit. So requesting a count could have a significant impact on governor limits, especially when working with many records. Now, if a query using one of these functions returns an integer, it only counts as one query row toward the governor limit. If a query using one of these functions returns an array of AggregateResult objects, only the total number of AggregateResult objects counts toward the limit.

https://resources.docs.salesforce.com/214/latest/en-us/sfdc/pdf/salesforce_summer18_release_notes.pdf