Help with SOQL grouping

Question

I have a requirement where I need to display top records from an object instead of all that meets the criteria. Below is my SOQL

SELECT  CustomAccount__r.Name, CustomAccount__r.Country__c, Month__c, 
                             Account_Range__c FROM Custom_Month__c WHERE Account_Range__c != 0 AND
                             CustomAccount__r.UpDate__c = TRUE AND Date__c >= LAST_MONTH 

Below is the result that I'm getting with the above SOQL

From the above result, I need to display only top records instead of all 4. It is as follows.

When I tried to group the query using Name, Account_Range__c and Month__c, I am recieving the error as

MALFORMED_QUERY: duplicate alias: Name

Can anyone help me out on this to display only top records of an account instead of multiple records?

Answer 1

SELECT CustomAccount__r.Name, CustomAccount__r.Country__c, Month__c, 
       Account_Range__c FROM Custom_Month__c 
WHERE Account_Range__c != 0 AND CustomAccount__r.UpDate__c = TRUE AND Date__c >= LAST_MONTH 
ORDER BY Account_Range__c DESC, Account_Range__c DESC

Use above query and the first record will the largest.

Answer 2

You can use aggregate MAX SOQL function.

You can use below query to get maximum values per account:

SELECT  CustomAccount__r.Name AccountName, MAX(Account_Range__c) MaxRange
FROM Custom_Month__c 
WHERE Account_Range__c != 0 AND CustomAccount__r.UpDate__c = TRUE AND Date__c >= LAST_MONTH 
GROUP BY CustomAccount__r.Name

Note that you should either aggregate a field or group the field and so you should not use CustomAccount__r.Country__c field.