1

I need query on Case and its child object 'Case Status History' such that I need cases whose Status='closed' and I need the latest 'Case Status History' record for each case. The 'Name' field of 'Case Status History' record is of Autonumber type so I am trying to get the largest name records of 'Case Status History' for each case. I was trying the bellow query but its not working.

Select Id , Subject , Status , 
       ( Select Id,Name,parent_case__r.subject 
         FROM Case_Status_History__c 
         where isdeleted = false group by name DESC limit 1 ) 
  FROM Case where Status ='Closed'

Is it possible to get records by SOQL in the way I want ?

Thanks!

  • 1
    the above query will work if you remove the group by name from inner query.. I guess – Ratan Paul Apr 20 '16 at 8:04
  • @sfdcweb Select Id , Subject , Status , ( Select Id,Name,parent_case__r.subject FROM Case_Status_History__c where isdeleted = false DESC limit 1 ) FROM Case where Status ='Closed' . Even this is not working. – Ruchi Apr 20 '16 at 8:07
  • I am receiving the error 'Unknown error parsing query' – Ruchi Apr 20 '16 at 8:08
  • Ruchi .. one more issue you need to specify relationship name of Case_status_history object.. I think that will be Case_Status_History__r so your inner query will be like Select Id , Subject , Status , ( Select Id,Name,parent_case__r.subject FROM Case_Status_History__r where isdeleted = false DESC limit 1 ) FROM Case where Status ='Closed' – Ratan Paul Apr 20 '16 at 8:40
1

Change your query to this:

SELECT Id, Subject, Status, 
   ( SELECT Id, Name, parent_case__r.Subject 
     FROM Case_Status_History__c 
     WHERE isdeleted = false LIMIT 1 ) 
FROM Case 
WHERE Status ='Closed'

(You removed GROUP BY but didn't remove the DESC part)

However, you might want to change the original GROUP BY Name DESC to ORDER BY Name DESC ie:

SELECT Id, Subject, Status, 
   ( SELECT Id, Name, parent_case__r.Subject 
   FROM Case_Status_History__c 
   WHERE isdeleted = false ORDER BY Name LIMIT 1 ) 
FROM Case 
WHERE Status ='Closed'
  • Harner : Is it possible to get the same thing in apex ? Means I want all the largest 'Case_Status_History__c records' for each CASE. Is that possible ? – Ruchi Apr 20 '16 at 10:31
  • Yes, you can query this to a list of Cases. You can then access the Case_Status_History__c record - it's normally a list within the list, but I'm not totally sure what it is if you limit it to 1... prob still a list. – Caspar Harmer Apr 20 '16 at 18:08

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.