[SalesForce] Ordered field must be grouped or aggregated

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?

Best Answer

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.

Related Topic