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!

4
  • 1
    the above query will work if you remove the group by name from inner query.. I guess
    – Ratan Paul
    Commented Apr 20, 2016 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
    Commented Apr 20, 2016 at 8:07
  • I am receiving the error 'Unknown error parsing query'
    – Ruchi
    Commented Apr 20, 2016 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
    Commented Apr 20, 2016 at 8:40

1 Answer 1

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'
2
  • 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
    Commented Apr 20, 2016 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. Commented Apr 20, 2016 at 18:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .