0

I have a custom field called Defect Number on the Case object. Multiple Cases can have same Defect Number. I want to write a query that gets the first Case created for each defect number. Any help?

  • How do you define first? – Girbot Aug 22 '16 at 19:09
  • Hi. Thanks for responding. First means the case corresponding to a defect that was created first. Currently, I am doing following : 1) List <AggregateResult> firstCaseList = [SELECT Defect_Number__c, min(createdDate) from Case group by Defect_Number__c ]; 2) Loop Through firstCaseList and Create a Map of defectNumber and CreatedDate. 3) For each defectNumber in map find CaseId with corresponding createdDate....Its a very Sad way....I would have used Inner join in SQL but dont know how to create the same in SOQL – Mudmayee Chaturvedi Aug 22 '16 at 19:25
  • You don't need to keep deleting your previous comments. In fact, that is making it harder for people to help you. Your most recent comment, where you include you two current SOQL queries, is a great example of something that should be edited in to your original question. – Derek F Aug 22 '16 at 19:28
2

As you're finding out, SOQL is different than standard SQL.

I don't think there is a way to achieve what you're looking to do with SOQL alone. I believe that you will need to query once, and then loop over your results to get the earliest created record. Assuming Defect_Number__c is a plain text field.

Map<String, Case> earliestCases = new Map<String, Case>();

for(Case currentCase :[SELECT CreatedDate, Defect_Number__c FROM Case]){
    if(!earliestCases.containsKey(currentCase.Defect_Number__c)){
         earliestCases.put(currentCase.Defect_Number__c, currentCase);
    } else {
        if(currentCase.CreatedDate < earliestCases.get(currentCase.Defect_Number__c).CreatedDate){
            earliestCases.put(currentCase.Defect_Number__c, currentCase);
        }
    }
}

The query I used above is not selective, which means that it doesn't do enough to limit the number of rows that it returns.

Salesforce allows you to query for no more than 50,000 rows in a single, normal synchronous transaction (and throws an uncatchable System.LimitException). Beyond that, If you have over 100,000 Cases, Salesforce will throw a System.QueryException (non-selective query against large object).

Probably the best way to make this query more selective would be to restrict the Defect_Number__c values you query for like this:

// new Set<type>{values} is how to provide initial values to a 
//   collection on a single line
Set<String> targetDefectNumbers = new Set<String>{'123', '25', '42', '1337'};

// The <field name> IN :<variable> syntax is called variable binding.
// Similar to a parameterized query in other SQL dialects.
List<Case> results = [SELECT CreatedDate, Defect_Number__c FROM Case WHERE Defect_Number__c IN :targetDefectNumbers];
  • Just an FYI, you can get the NSQ error with only a few records populating the object. The threshold is not defined. Only that a query is NS. – Eric Aug 22 '16 at 20:30
  • @Eric do you have a reference for that? I am fairly certain you can't get that query on less than 100k records. – Adrian Larson Aug 22 '16 at 20:50
  • @AdrianLarson - Nope, just from experience a while ago. able had only 500 and some odd number of rows and received the error. Was told at the time that the table did not have to have 100K rows in it, just the possibility that the query would return more than 10% or something like that. Wish I had proof or was able to reproduce it. At the time I was also told that there is no set number of records that would cause it to always appear if < 100K rows existed. (thus cannot replicate per se). Maybe things have changed. Been a while since I wrote bad queries like that – Eric Aug 22 '16 at 21:50
  • I've had configuration tables with more rows than that...and was able to run queries on them unfiltered. I have to think it's less mysterious than that. – Adrian Larson Aug 22 '16 at 21:54
  • 2
    @Eric The best resource I've come across for this would be the query optimization cheat sheet, That's more about which filters in a WHERE clause would cause a query to be/not be selective though. Doesn't address when there isn't a WHERE clause. – Derek F Aug 22 '16 at 22:20

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.