function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
IntegratorIntegrator 

SOQL query for the most recent ActivityDates for each contact in a set

I'd like to get the dates of the most recently completed activities for each of a set of contacts by using a single SOQL query.

I realize that I could fetch all completed activities for those contacts and iterate through them to find the most recent ones, but I'm wondering if it's possible to do this in a single query that only returns the data I need.

If I were writing SQL instead of SOQL, I would do something like the examples below. (I've omitted the where clauses for simplicity.)

select WhoId, Max(ActivityDate) 
from Task
where ... 
Group By WhoId

 or less optimally,

select WhoId, 
   (select top 1 from ActivityDate from Task T2 where T1.Id = T2.Id order by ActivityDate desc)
from Task T1
where ...
Group By WhoId

 

Neither of these approaches seems to translate directly to SOQL. Is there an alternative?


raseshtcsraseshtcs

You can use "order by" clause in the query and use the first result to get the most recent completed activity
IntegratorIntegrator

Thanks raseshtcs. I'm trying to get the most recent activity date for each contact. My original post might have been misunderstood on that point; I've updated it to clarify.

 

ActivityDate can't be ordered by without being grouped or aggregated.

 

Grouping by both WhoId and ActivityDate will return the dates for all activities, which is what I'm trying to avoid. A "limit x" clause won't help because that would just give me the x most recent activities, not the most recent activity for each contact.

 

I'd love to aggregate it (i.e., call "MAX(ActivityDate)"), but it doesn't seem to be supported. The API documentation here indicates that MAX() supports date types, however when I try to save the Apex I get the error "field ActivityDate does not support aggregate operator MAX".
jeremyyjeremyy

I'm having the same issue. Is this a bug? Is there a workaround?
SuperfellSuperfell

Can't you just use the LastActivityDate field on contact? e.g. 

select name,lastActivityDate from contact
jeremyyjeremyy

I don't think so. My use case is something like this:

 

SELECT MAX(ActivityDate), WhatId, CustomPickListField__c 
FROM Task 
WHERE WhatId IN :SomeIds 
GROUP BY WhatId, CustomPickListField__c

 SomeIds is a set of Account Ids. Any suggestions?
Matt QuinlanMatt Quinlan
It's July 2014 and this is STILL broken!
MellycooksMellycooks
Does anyone know if this is documented as a limitation anywhere?  I couldn't find it in known issues and our support level isn't high enough to let me ask APEX questions or even flag bugs (thanks SF)
Nathan HincheyNathan Hinchey
There's an Idea about this: Support aggregate operators MIN and MAX for ActivityDate (https://success.salesforce.com/ideaView?id=0873A000000lJtWQAU)