2

I have this SOQL, I'm trying to also get the ID but I get this error

Field must be grouped or aggregated: Id

How can I also get the ID?

SELECT count(ID), MAX(CreatedDate)
FROM SAR__c 
WHERE Activity__c = 'Play'
GROUP BY New_External_ID__c

1 Answer 1

10
SELECT New_External_ID__c,count(ID), MAX(CreatedDate)
FROM SAR__c 
WHERE Activity__c = 'Play'
GROUP BY New_External_ID__c,Activity__c 

Try the above query.Any field thats included in Aggregate SOQL has to be either aggregated or grouped .

Now in the above query you have used Activity__c but thats not aggregated or grouped .Id field you have already Aggregated so you cannot Group By .

Also i assume New_External_ID__c is an external Id in salesforce so if you want count you can always use list.size() to get count .It is not making sense to group by External Id as each record will have unique external Id

If you want Id also then you will have to remove COUNT and use as below

SELECT New_External_ID__c,ID, MAX(CreatedDate)
FROM SAR__c 
WHERE Activity__c = 'Play'
GROUP BY New_External_ID__c,Activity__c,ID

Update:

As explained in the comment by question author providing logic to meet requirement:

Map<Id,Id> mapIDBySetIds=new Map<Id,SET<Id>>();
for(SAR__c  s:[SELECT ID, CreatedDate,New_External_ID__c FROM SAR__c WHERE Activity__c = 'Play' order By CreatedDate]){

 if (!(mapIDBySetIds.containsKey(s.New_External_ID__c))) {
      mapIDBySetIds.put(s.New_External_ID__c,s.id)
 } 

}//formation of map of recent Ids for externalIds that are not unique
2
  • Basically what I am trying to do is get the latest record for each New_External_ID__c (in my case there might be multiple records with the same New_External_ID__c). How should I go about doing so?
    – Dedo
    Aug 25, 2013 at 14:12
  • 1
    updated my answer .I think you can get away with aggregated function logic thought as that can make solution complex .Stick with basics of Maps and order By clause and that would do Aug 25, 2013 at 15:19

You must log in to answer this question.

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