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
sk_63sk_63 

need help in aggregate query

I have a custom apex page where the records will be displayed every month for a logged in user. They are as follows:
User-added image
Now, my requirement is to display only max records of an Account instead of all the records. It should be as follows:
User-added image
Below is my controller:
public class PD_ClosedAccounts {
    public String accId {get;set;}
    public String accId2 {get;set;}
    public list<Custom_Month__c> accList2 {get; set;}
    public List<Custom_Month__c> CustomMonthList {get;set;}
    public list<string> dID = new list<string>();
    public map<Id,string> CusAccID = new map<Id,string>();
    public List<User> usr = new List<User>();
    
    public PD_ClosedAccounts(){
        // Get the logged in user's Account Id
         usr = [Select Id, PD_Acc__c From User Where Id=:UserInfo.getUserId()];
        
        if(usr != null ) {
            
            accId = usr[0].PD_Acc__c;
            
            Set<Id> allCusAccIDs = new Set<Id>();
            allCusAccIDs.add(accId);   

            
            // Get all the associated Acc records of the logged in user:
            for(Account associatedAcc: [Select ID, Cus_Acc_ID  From Account Where Id = :accId] ){
                CusAccID.put(associatedAcc.ID, associatedAcc.Cus_Acc_ID);
            }
            
            
           dID=CusAccID.values();
            
            CustomMonthList = [SELECT Name, Acc__c, Acc__r.Name, Acc__r.Acc_Id_Format__c,   
                             Acc__r.Level__c, Acc__r.Location_Country__c, Processing_Month__c, 
                              Acc_c__c FROM CustomMonthList WHERE Acc_c__c != 0 AND
                              Processing_Date__c >= LAST_MONTH 
                              AND Acc__r.Spn_Id__c IN :dID order by Acc__r.Acc_Id_Format__c DESC];
            
        }
		 AggregateResult[] groupedResults = [SELECT id id, 
                                            MAX(Acc_c__c) maxAccountC 
                                             FROM CustomMonthList WHERE ID IN:CustomMonthList 
                                               group by  Processing_Date__c, 
                                            Acc__r.Acc_Id_Format__c];
                
        List<Id> theIds = new List<Id>();

        for (AggregateResult result : groupedResults) {
            theIds.add((Id) result.get('id'));
        }
        accList2 = [SELECT Acc__c, Acc__r.Acc_Id_Format__c  FROM Custom_Month__c WHERE ID IN :theIds];
        if(accList2!= Null){
            for(integer i=0;i<accList2.size();i++){
         accId2 =  accList2[i].Acc__c;
            } 
        }
    }
}


From the above controller, even though I have aggreagte query fetching max of Account_c__c field from CustomMonthList, I am getting all the 4 records in my page instead of Max ones.

Can anyone suggest the changes in my code so that I can able to fetch only max records?

Thank you in advance
SK

Ajay K DubediAjay K Dubedi
Hi SK,

Replace by this  code:

AggregateResult[] groupedResults = [SELECT Id, 
                                           MAX(Acc_c__c),
                                           maxAccountC 
                                           FROM CustomMonthList 
                                           WHERE ID IN:CustomMonthList 
                                           group by  Processing_Date__c, 
                                           Acc__r.Acc_Id_Format__c];

OR

Follow this code:

AggregateResult[] groupedResults = [SELECT Id, 
                                           Acc_c__c,
                                           maxAccountC 
                                           FROM CustomMonthList 
                                           WHERE ID IN:CustomMonthList 
                                           group by  Processing_Date__c, 
                                           Acc__r.Acc_Id_Format__c 
                                           ORDER BY Acc_c__c DESC];

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

Thanks and Regards,
Ajay Dubedi
www.ajaydubedi.com
sk_63sk_63
Hi Ajay,

I tried your suggested code, but that didn't work for me.
I'm getting the following error: 
Ordered field must be grouped or aggregated: Acc_c__c

Can you please suggest any modifications.

Thanks,
SK​​​​​​​
sk_63sk_63

Adding to my above comment,
My requirement is to display only maximum records from each account. As per your given query, I will be displaying max records on the top. I'm afraid, that will not suffice my requirement