soql question - use group by ? [ Edited ]

[Sdry]

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!

[TheIntegrator]

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?

[Sdry]

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 ?

[TheIntegrator]

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

[Sdry]

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.

[TheIntegrator · Accepted solution]

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.

["Man"ish]

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