I'm trying to order an aggregated query by a date field and Salesforce is giving me conflicting errors.

In the below code I have max(Last_Referral_Date__c) with an order by Last_Referral_Date__c. Using the query below I get:

Ordered field must be grouped or aggregated

If I add Last_Referral_Date__c to the group by section I get:

Aggregated field can not be grouped by error.

 String sQuery = 'Select Referral_Partner__r.ID referralID, count(ID) numOfReferral,';
        sQuery += ' max(Account__r.Projected_Annual_Revenue__c) Billing,';
        sQuery += ' max(Last_Referral_Date__c) lastReferralDate from Referrals__c';
        sQuery += ' Group By Referral_Partner__r.ID   Having max(Last_Referral_Date__c) <Last_N_DAYS : 90';
        sQuery += ' Order by Last_Referral_Date__c ASC';

        List<AggregateResult> Referrals = Database.query(sQuery);
        for (AggregateResult ar :Referrals) {

            System.Debug('Date: ' + ar.get('Last_Referral_Date__c'));

I need to be able to order by the Last_Referral_Date__c field but am not sure how?


Since you are not grouping by that field, you can only order by an aggregation of that field. For example you can use ORDER BY MAX(DateField__c) but not ORDER BY DateField__c.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.