1

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 Without Grouping

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

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?

0
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.

  • I need only the large ones from Test Account 1 and Test Account 2 to be fetched instead of displaying the top ones first. Please suggest – Skumar Oct 11 at 9:46
  • Take List<Custom_Month__c > monthList = query; then monthList[0] will be largest – summer Oct 11 at 9:48
0

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.

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.