Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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.


share|improve this question
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
add comment

1 Answer

up vote 7 down vote accepted

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)

share|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
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
Thanks.. Will go with count() for now :) –  Prady Dec 12 '12 at 11:30
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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