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
SamuelDeRyckeSamuelDeRycke 

soql question - use group by ?

Hi, i have a soql statement as following: 

 

SELECT id, lookup_Id, lastmodifieddate FROM SomeTable WHERE project__c in ( conditional subquery )
AND lastmodifieddate = THIS_YEAR)
ORDER BY lookup_Id, lastmodifieddate DESC

 

This will give me an overview of all SomeTable records, ordered by lookup_ID and lastmodified date. However, I only need the most recent modified entries for each lookup_id from SomeTable, and am a bit lost on how to achieve this.

 

I tried the following, expecting it wouldn't work (and it didn't ), 

 

SELECT id, lookup_id, max(lastmodifieddate) FROM SomeTable WHERE project__c in (conditional subquery)
AND lastmodifieddate = THIS_YEAR)
GROUP BY lookup_id
ORDER BY lookup_id, lastmodifieddate DESC

 

I'm looking to extract follwing data "example":

 

11111, AAAA,  date   : where 11111 is the most recent modified record  from Sometable for lookup_id AAAA

22222,BBBB, date    : where 2222 is the most recent modified record for SomeTable for lookup_id BBBB

..

..

..

 

I'm probably overlooking something quite basic here, all help appriciated!

Best Answer chosen by Admin (Salesforce Developers) 
TheIntegratorTheIntegrator

ok, in that case, grouping ID along with lookup_id defeats the purpose and you can aggregate ID as Id value is needed. In my opinion, you will need to query the complete set and then handle this in Apex code.

All Answers

TheIntegratorTheIntegrator

Could you tell why this didn't work for you?

 

SELECT id, lookup_id, max(lastmodifieddate) FROM SomeTable WHERE project__c in (conditional subquery)
AND lastmodifieddate = THIS_YEAR)
GROUP BY lookup_id
ORDER BY lookup_id DESC

 

Logically this should give you what you are looking for, i.e the latest record with each lookup_id in SomeTable. Do you get error or do you get result differently?
SamuelDeRyckeSamuelDeRycke

that gives me a malformed query (like my own example), as id needs to be grouped or agregated, but when grouping on id, i have no results (while there should be results).

 

Maybe this just isn't possible with soql ?
TheIntegratorTheIntegrator

Have you tried

 

SELECT lookup_id.Name, max(lastmodifieddate) FROM SomeTable WHERE project__c in (conditional subquery)
AND lastmodifieddate = THIS_YEAR)
GROUP BY lookup_id.Name
ORDER BY lookup_id.Name DESC
SamuelDeRyckeSamuelDeRycke

The goal would be to have those ID field values, else the output becomese meaningless. lookup_id and the most recent lastmodifieddate are the filter creteria.
TheIntegratorTheIntegrator

ok, in that case, grouping ID along with lookup_id defeats the purpose and you can aggregate ID as Id value is needed. In my opinion, you will need to query the complete set and then handle this in Apex code.

This was selected as the best answer
sfdcFanBoysfdcFanBoy

I have a similar situation but I have to display as many as 8 fields. Here's the scenario. Hope you can help.

 

I have the following SOQL query to display List of ABCs.

 

Public List<ABC__c> getABC(){
    List<ABC__c> ListABC = [Select WB1__c, WB2__c, WB3__c, Number, tentative__c, Actual__c, PrepTime__c, Forecast__c from ABC__c ORDER BY WB3__c];
    return ListABC;
}

 

Here is the display.

 

As you can see in the above image, WB3 has number of records for A, B and C. But  I want to display only 1 record for each WB3 based on MAX(Actual__c).   Only latest Actual__c must be displayed for each WB3.

 

i.e., Ideally I want to display only 3 rows in this example (one each for A, B and C).

 

 

I have used GROUPBY and displayed the result using AggregateResults.

 

 

public List<SiteMonitoringOverview> getSPM(){
        AggregateResult[] AgR = [Select WB_3__c, MAX(Tentaive_Date__c) dtTentativeDate , MAX(Actual_Date__c) LatestCDate FROM Site_progress_Monitoring__c GROUP BY WBS_3__c];
        
        if(AgR.size()>0){                    
            
            for(AggregateResult SalesList : AgR){                                                        
                CustSumList.add(new SiteMonitoringOverview(String.ValueOf(SalesList.get('WB_3__c')), String.valueOf(SalesList.get('dtTentativeDate')), String.valueOF(SalesList.get('LatestCDate')) ));            
            }    
        }
        return CustSumList;
    }

 

I got the Latest Actual Date for each WB3.  But the Tentative date is not corresponsding to it.  The Tentative Date is also the MAX in the list. I am forced to use MAX() for tentative date.

 

( If I dont aggregate, it throws an error as 'Every column in the SOQL query must be either GROUPED or AGGREGATED'. That's weird.)

 

I dont need to get the MAX of Tentative Date. I want the Tentative Date corresponding to the Max(Actual__c). 

 

Here is the output of the above aggregateResult query

 

 

 

How should I proceed with this?  Can we do it without using AggregateResult? Please help.

 

Thanks