function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
kabkab 

SOQL with aggregate functions

Hi,

 I am trying to get the value of 3 fields and get the latest record by the contact id getting error, when I try to get the value of other field(Id) that is not in groupby clause

sobject[] res= [Select id,Contact__c,max(createddate) from Custom_Object__c group by Contact__c];

This is general syntax also in other sql. Dow to get the ID of the record which is the latest.

 

Is there any soql I can use which would give me the result with(ID, Contact__c)?

Contact__c could have multiple records.

 

 


.

 

 

Ralph CallawayRalph Callaway

The aggrate results only contains the group by field, in this case contact__c, and the aggregatated summary, in this cas max(createddate).  It sounds like you're not trying to aggregate and would want to use an order by clause to get the most recent object instead of using group by.

 

This seems like the query you'd want:

Custom_Object__c[] result = [select id, contact__c, createdDate from Custom_Object__c order by CreateDate desc limit 1]; 
kabkab

Sorry this will work only for one record and I need to get records for multiple contactid.Is there any soql you can think of?
Ralph CallawayRalph Callaway

I think I know what you're going for.  You want to query the via the contact object and then do an ordered subquery for your custom object.  If I knew my database theory better I could explain what type of join this is, but alas my database work started with Salesforce.  Here's an example query that returns the most recently created custom object for each contact with a couple fields from the contact object.  I don't know you're exact schema so you'll need to adapt this query to your needs.

 

List<Contact> contactsWithMostRecentCustomObject =
  [select id,
    (select id, field1__c, field2__c
     from Custom_Objects__r
     order by createdDate desc
     limit 1)
   from Contact]
for(Contact contact : contactsWithMostRecentCustomObject) {
  List<Custom_Object__c> customObjects = contact.Custom_Objects__r;
  Custom_Object__c mostRecentCustomObject;
  // be careful since the contact may not have any child custom objects
  if(!customObjects.isEmpty())
    mostRecentCustomObject = customObjects[0];
  String field1 = mostRecentCustomObject.field1__c;
  String field2 = mostRecentCustomObject.field2__c;
}