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