Need help with SOQL query

Question

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!

Answer 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'