Here is a situation where i need to get the last date of apppointment for a contact and i also need the id of the last appointment date.

All appointments are stored in custom object and has a lookup to contact.

  SELECT Max(startDate__c ) FROM appointments Group by contact__c

IS there a way to get id of the appointment for which the max(startDate__c) is returned? I want to have for each contact which is the latest appointment date and the id of the latest appointment.


  • SELECT Contact__c ContactId, MAX(StartDate__c) StartDate FROM Appointments__c GROUP BY Contact__c would do this, but the subquery method uses fewer rows. – sfdcfox Jul 31 '13 at 20:41

Why not with just a subquery?

SELECT Id, Name,
    (SELECT Id, StartDate__c FROM Appointments__r ORDER BY StartDate__c DESC LIMIT 1)
FROM Contact

Keep it simple, aggregate functions "waste" query rows (for example COUNT() will return only 1 row but will use up N rows from the limit)

| improve this answer | |
  • just deviating a bit, is there a way other than using aggregate function to get count. ? – Prady Dec 12 '12 at 8:30
  • 1
    Non-row wasting you mean (so no [SELECT...].size() and such stuff)? I'm not aware of any Apex-y tricks. If you can plan ahead - rollup summary field (can be with conditional logic), maybe have a look at Analytic Snapshots (basically you schedule feeding of some reports output into a helper object and later you can rely on this helper object with "flattened" data in Apex or other reports). And last but not least - Setup -> Storage Usage ;) – eyescream Dec 12 '12 at 9:25
  • if start_date__c can be null then you need to use: SELECT Id, Name, (SELECT Id, StartDate__c FROM Appointments__r WHERE start_date__c != null ORDER BY StartDate__c DESC LIMIT 1) FROM Contact otherwise the Appointments with null start_date__c will be returned first as the 'highest' value – cropredy Jun 18 '14 at 20:53
  • 2
    @crop1645 fair point, that's one way although SF doesn't always like filtering out nulls (non-selective query...). Another option would be to ORDER BY StartDate__c NULLS LAST. No idea which one would be better performance-wise. – eyescream Jun 18 '14 at 21:40

first group by startdate and then you got left is 1 record from that run again max(id) that will return id

SELECT Max(startDate__c ), max(id) 
FROM appointments 
Group by contact__c
| improve this answer | |
  • 2
    no, it's not correct this will get the Max startDate__c and MAX of ID from different records with the same contact__c – newBike Oct 24 '17 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.