function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Arek S.Arek S. 

SOQL query that excludes records that have two identical fields

I'm running a query on LeadHistory that looks like this:

 

      leadHistories = new List<LeadHistory>(
                      [SELECT LeadId, CreatedDate, Field, NewValue
                       FROM LeadHistory
                       WHERE CreatedDate >= :mFromDate 
                       AND CreatedDate <= :mToDate 
                       AND LeadId IN (SELECT Id
                                      FROM Lead
                                      WHERE Primary_Campaign_Source__c = NULL)
                       ORDER BY CreatedDate DESC]);

 I need it to exclude objects that have the same value for LeadId and NewValue.  So for example take the following three objects:

 

LeadHistory - ID: 0000000001, LeadId: 1000000000, CreatedDate: Jan 1, 2012, Field: Status, NewValue: Closed

LeadHistory - ID: 0000000002, LeadId: 2000000000, CreatedDate: Jan 2, 2012, Field: Status, NewValue: Closed

LeadHistory - ID: 0000000003, LeadId: 2000000000, CreatedDate: Jan 3, 2012, Field: Status, NewValue: Unclosed

LeadHistory - ID: 0000000004, LeadId: 2000000000, CreatedDate: Jan 4, 2012, Field: Status, NewValue: Closed

 

I want my query to only return the first, second, and thrid objects, but not the fourth one since it has the same value for LeadId and NewValue as the second object.

 

Any help with this is appreciated,

Thanks.

 

bob_buzzardbob_buzzard

You should be able to do this by storing the records in a map keyed by a string made up of leadid and newvalue.  That way you can check if there is already a value with the same key and skip adding the record to the map.

sfdcFanBoysfdcFanBoy

@bob_buzzard: how can we check if there is already a value with the same key? can you please eloborate with some simple code.  Thanks!

bob_buzzardbob_buzzard

It should be something like the following (caveat emptor - not compiled so you may not be able to just cut and paste this in):

 

List<LeadHistory> leadHistories = new List<LeadHistory>(
                      [SELECT LeadId, CreatedDate, Field, NewValue
                       FROM LeadHistory
                       WHERE CreatedDate >= :mFromDate 
                       AND CreatedDate <= :mToDate 
                       AND LeadId IN (SELECT Id
                                      FROM Lead
                                      WHERE Primary_Campaign_Source__c = NULL)
                       ORDER BY CreatedDate DESC]);

Map<String, LeadHistory> lhByCompositeKey=new Map<String, LeadHistory>();
for (LeadHistory lh : leadHistories)
{
   String compKey=lh.LeadId + ':' + lh.NewValue;

   // only insert into the map if the composite key hasn't been
   // encountered before
   if (null==lhByCompositeKey.get(compKey))
   {
      lhByCompositeKey.put(compKey, lh);
   }
}

return lhByCompositeKey.values();

 

sfdcFanBoysfdcFanBoy

Hi Bob.  Thanks a lot. thats useful, but doesn't help in completing the requirement.

 

I have this SOQL inner query and using wrapperclass I am displaying the result in visualforce page(as shown below).

 

 for(Project__c a:[SELECT Id, Project_Coordinator__c,Project_Coordinator__r.Name,Project__c.Name,       
      (SELECT Id,Name,WBS_1__c, WBS_2__c,WBS_2_INT__c, WBS_3__c, Casting__c,Tentative__c, Actual___c, Prep_Time__c, Forecast__c FROM Progress_Monitoring__r ORDER BY WBS_1__c, WBS_3__c, WBS_2_INT__c DESC)
       FROM Project__c where Project_Coordinator__r.Name=:selectedPC])

Project Name: ABC Clinic

 

As you see, the column WB3 has number of rows for A, B and C.  But I want to display only 1 row for each WB3, displaying the latest Actual Date row for each WB3.  i.e., Ideally I want to display only 3 rows in this example.  I have to limit the rows with in a group.

 

I tried grouping but I got the error: Cannot use GROUP BY in inner queries.

 

So, I tried Aggregate functions(AggregateResult collection) without inner quries, but cannot get the exact result as "All the columns in the query must be either GROUPED or AGGREGATED." ( I dont want to aggregate every column)

 

Using standard reports I can build this summary report, but cannot limit the rows per each grouping. "Limit" limits the rows for the entire report, not within a group! 

 

All doors closed! How do I go about this?  What other logic can I use?

 

Please share your thoughts,

 

Thanks.

bob_buzzardbob_buzzard

I'm a bit confused - your original requirement was only to return one record per unique composite key.  Are you now saying the requirement is that it must be done using SOQL?

bob_buzzardbob_buzzard

Actually, I've just realised this is a thread hijack.  Can you post your question under its own thread.

sfdcFanBoysfdcFanBoy

Actually i tried to use your solution for my requirement.