[SalesForce] SOQL query minimum Date in WHERE clause

trying to get field with the earliest date from custom object. Here is my current query

BomaGroups =    [SELECT Name, Location__c FROM Group__c WHERE Location__c =:id]; // Selects the Group I want from Location Master object
MeetingsList2 = [SELECT Name, Group__c, Business_Value__c, Meeting_Date__c, GPS_Meeting_Location__Latitude__s, GPS_Meeting_Location__Longitude__s, 
Group__r.Name FROM Meeting__c WHERE Group__c =:BomaGroups 
ORDER BY Meeting_Date__c DESC LIMIT 1]; // Selects Meetings I want with the Groups SOQL  as the where clause

I have 3 custom objects, Location(master), Groups(child), meeting(grand child). The above query works fine for getting 1 record but its supposed to return only the earliest date values for all the records matching the where clause. I was thinking of inserting an SOQL statement in the where clause for minimum date, any clues?

Tried the following subquery to combine the 2 statements, generated Error: Didn't understand relationship 'Group__r' 

BomaGroupsList = [select Name, Location__c, (SELECT Name, Group__c, Business_Value__c,       Meeting_Date__c, GPS_Meeting_Location__Latitude__s,GPS_Meeting_Location__Longitude__s FROM Group__r ORDER BY Meeting_Date__c DESC LIMIT 1) FROM Group__c WHERE Location__c =:id];

Best Answer

Try this:

list<Group> groups = [select id, name, (SELECT Name, Group__c, Business_Value__c, Meeting_Date__c, GPS_Meeting_Location__Latitude__s, GPS_Meeting_Location__Longitude__s FROM Meetings__r ORDER BY Meeting_Date__c DESC LIMIT 1
 ) from Group where Id =:BomaGroups];

Your limit 1 in the original query applied to the whole of the result set and therefore only returned 1 record. By using a sub-query you can get the records that relate to the groupIds you need. I have guessed at the lookup relationship api name being Meetings__r - this may need adjusting.

Related Topic