Body: <p>I'm running a SearchKit with 2 stages:</p>
  <p>Firstly, find contacts who completed a specific activity within a certain date range. Easy.</p>
  <p>Then, for those contacts, pull results for other activities completed by them, even if they fall outside of the previously specified date range. Also easy.</p>
  <p>However, I have two issues with my results:</p>
  <ol>
  <li><p>Some contacts have more than 1 case, so I need to pull only the activity results for the most recent case.</p>
  </li>
  <li><p>Similarly, some contacts have completed the same activity twice on the same case. Again, I need to pull results for only the most recently completed activity.</p>
  </li>
  </ol>
  <p>Both problems could be sorted by finding the most recently completed activity. However, as far as I can tell, there's no way of filtering date by 'most recent'?</p>
  <p>I've tried using 'min' instead of 'list' in the field transformations (see <a href="https://civicrm.stackexchange.com/questions/44618/searchkit-filter-activity-results-in-a-query-to-show-activity-with-earliest-dat?rq=1">Searchkit: filter activity results in a query to show activity with earliest date</a>), but this gives variable results and doesn't work for numerical values (instead just giving the minimum value, as it should).</p>
  <p>Below is a screenshot of my current search:</p>
  <p><a href="https://i.sstatic.net/CD7uo.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/CD7uo.png" alt="screenshot of search" /></a></p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-19T16:32:34.153Z
Comments.Text: I've run into the same limitation! If you *only* need the activity date, then it's easy, just GROUP BY `Contact.id` and select MAX activity date as your filter. But it gets trickier when you want other fields. I think we really need to add FIRST and LAST field transformations. Any chance your org could sponsor that work? (I work for the CiviCRM Core Team btw and we're always happy to work on sponsored improvements).
Comments.UserDisplayName: Coleman
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-20T08:43:19.820Z
Comments.Text: I'm afraid we're a small charity so wouldn't have the funds for this. Is there a way of putting a shout out for other organisations to sponsor an improvement?
Comments.UserDisplayName: Sally
Tags: <searchkit><activity><date-preferences>
Title: Is it possible to filter activity date by 'most recent'?
ThreadText: # Is it possible to filter activity date by 'most recent'?
  
  (Asked by: username_0 on 2023-10-19)
  
  I'm running a SearchKit with 2 stages:
  
  Firstly, find contacts who completed a specific activity within a certain date range. Easy.
  
  Then, for those contacts, pull results for other activities completed by them, even if they fall outside of the previously specified date range. Also easy.
  
  However, I have two issues with my results:
  
  1. Some contacts have more than 1 case, so I need to pull only the activity results for the most recent case.
  2. Similarly, some contacts have completed the same activity twice on the same case. Again, I need to pull results for only the most recently completed activity.
  
  Both problems could be sorted by finding the most recently completed activity. However, as far as I can tell, there's no way of filtering date by 'most recent'?
  
  I've tried using 'min' instead of 'list' in the field transformations (see [Searchkit: filter activity results in a query to show activity with earliest date](https://civicrm.stackexchange.com/questions/44618/searchkit-filter-activity-results-in-a-query-to-show-activity-with-earliest-dat?rq=1)), but this gives variable results and doesn't work for numerical values (instead just giving the minimum value, as it should).
  
  Below is a screenshot of my current search:
  
  [![screenshot of search](https://i.sstatic.net/CD7uo.png)](https://i.sstatic.net/CD7uo.png)
  
  **Tags:** searchkit, activity, date-preferences
  
  > **Comments:**
  > **username_1**: I've run into the same limitation! If you *only* need the activity date, then it's easy, just GROUP BY `Contact.id` and select MAX activity date as your filter. But it gets trickier when you want other fields. I think we really need to add FIRST and LAST field transformations. Any chance your org could sponsor that work? (I work for the CiviCRM Core Team btw and we're always happy to work on sponsored improvements).  
  > **username_0**: I'm afraid we're a small charity so wouldn't have the funds for this. Is there a way of putting a shout out for other organisations to sponsor an improvement?  
  
  ---
  
  ## 0 answers
  
  