+ Start a Discussion
Arun BArun B 

Max(Date Column) : is it possible in SOQL

Hello there,
I wanted to find out the max(of a list of Dates) .e.g: Select PSOD_Project__r.Name,Resource__r.Name,max(Period_Beginning__c) from Time_Sheet__c where PSOD_Project__r.Name = 'PSOD Internal'

This is not working; Any hints whether this is feasible at all in SOQL ? if so, help pls.



Thanks,
Arun B
Arun BArun B
Anyone up for this ??
jrotensteinjrotenstein
Are you only expecting to get back one record? If so, try:

Code:
SELECT PSOD_Project__r.Name,Resource__r.Name, Period_Beginning__c
FROM Time_Sheet__c
WHERE PSOD_Project__r.Name = 'PSOD Internal'
ORDER BY Period_Beginning__c DESC
LIMIT 1

 

Arun BArun B
Thanks for the response jrotenstein. I indeed need to retrieve multiple records for different where conditions. I cannot limit the rowset by 1. Any other approach do you suggest ?



Thanks,
Arun B
jrotensteinjrotenstein
There's no SOQL equivalent to max(), so you're stuck with loading ALL relevant records and sorting it out via Apex code. Easiest would be to load in Date desc order, loop through and only pay attention when a new Resource is mentioned.
Arun BArun B
Yes I just did like that .. thanks for the promt responses jrotenstein.


Regards,
Arun B
md1md1

I had the same challenge and most searches about it let to this post so I thought I'd post the solution here. My problem was to select from the latest ActivityDate on Tasks or Activities and update on the related Contact (person account).

 

Here's what I did to get around all limitations:

 

- Create a custom Last Activity Time string field on the Activity record

- Since you can't access the ActivityDate field in formulas, update the above field using before triggers on both Task and Event with the long values of the field

 

 

trigger UpdateEventTimeField on Event (before insert, before update) {
    
    for (Event ev : Trigger.new){
    Datetime myDate = ev.ActivityDate;
        if(ev.ActivityDate != null)
            ev.Activity_Time__c = String.valueOf(myDate.getTime());
    }
}

 and similarly for Task:

 

trigger UpdateEventTimeField on Task (before insert, before update) {
    
    for (Task t : Trigger.new){
    Datetime myDate = t.ActivityDate;
        if(t.ActivityDate != null)
            t.Activity_Time__c = String.valueOf(myDate.getTime());
    }
}

 

Now that we have the latest time on the fields, have similar triggers on both Event and Task to get the maximum of the fields and update accordingly.

 

 

 

trigger UpdateContactFromEvent on Event (after insert, after update) {

    Set<Id> contactsToUpdateActivityDate= new Set<Id>();
    
    for (Event ev : Trigger.new){ 
        if(ev.WhoId!=null && 
        (((String)ev.WhoId).startsWith(contact_prefix) ||
        ((String)ev.WhoId).startsWith(account_prefix)
         && ev.WhatId != null){
                
            if(Trigger.isUpdate){
                    Id ocId = Trigger.oldMap.get(ev.Id).WhoId;
                    if(ocId != ev.WhoId)
                    contactsToUpdateActivityDate.add(ocId);
            }
                contactsToUpdateActivityDate.add(ev.WhoId);
                
            }
                        
            contactsToUpdate.add(c);
        } 
    }
    
    //Set the value of the Last Modified Date of the Contacts to the
    //latest ActivityDate of the closed Tasks OR most recent Event (whichever is latest)
    //related to that Contact
    
    if(contactsToUpdateActivityDate.size()>0){
        Map<string, Contact> cMap = new Map<string, Contact>();
        
        
        AggregateResult[] events = [select WhoId, Max(Activity_Time__c) from Event where WhoId IN :contactsToUpdateActivityDate GROUP BY WhoId];
        if(events.size()>0){
            for(AggregateResult e: events){
                if(e.get('WhoId')!=null && e.get('expr0')!=null)
                    cMap.put((string)e.get('WhoId'), new Contact(Id=(string)e.get('WhoId'), Last_Activity_Date__c=datetime.newInstance(long.valueOf((string)e.get('expr0')))));
            }
        }
        AggregateResult[] tasks = [select WhoId, Max(Activity_Time__c) from Task where WhoId IN :contactsToUpdateActivityDate GROUP BY WhoId];        
        if(tasks.size()>0){
            for(AggregateResult e: tasks){
                if(e.get('WhoId')!=null && e.get('expr0')!=null)
                    cMap.put((string)e.get('WhoId'), new Contact(Id=(string)e.get('WhoId'), Last_Activity_Date__c=datetime.newInstance(long.valueOf((string)e.get('expr0')))));
            }
        }
        system.debug(cMap.values());
        update(cMap.values());
    }    
    
    if(contactsToUpdate.size()>0)
        update new List<Contact>(contactsToUpdate);
    
}

 

 

....and the same thing for Task and you're done! 

 

hope this helps!

 

-Manu

watrowatro

I am actually trying to do the same thing but just a simple correctionMAX() in SOQL is there, it works just fine for numeric values, but it seems to have problems with date fields. 

 

You can look at all available aggregate functions here: Agregate Functions Docs

 

 

DodiDodi

This works with dates using aggregate functions.

 

Select MAX(createddate) From Opportunity

 

Depending on your verion of the IDE it may not show a result, but executing in the Developer console will render a result.

 

Fahd

 

cloudras.com

Sheida Saedifaez 5Sheida Saedifaez 5
i know i am really late to this thread, but this is what i did
Select createdDate from opportunity order by createdDate desc limit 1
this will give us the opportunity with the latest create date. Wouldn't it?

Do i need to use aggregagte function?
Nathan HincheyNathan Hinchey
There's an idea now to support aggregate operations on ActivityDate (https://success.salesforce.com/ideaView?id=0873A000000lJtWQAU)