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.

Thanks

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 1

up vote 9 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
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
    
Thanks.. Will go with count() for now :) –  Prady Dec 12 '12 at 11:30
    
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 –  crop1645 Jun 18 at 20:53
1  
@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 at 21:40
add comment

Your Answer

 
discard

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.