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
Attribution to: Prady
Possible Suggestion/Solution #1
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
Attribution to: anonymously
Possible Suggestion/Solution #2
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)
Attribution to: eyescream
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/5321