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?

2 Answers 2

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.

2
  • 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
    Commented Oct 11, 2019 at 9:46
  • Take List<Custom_Month__c > monthList = query; then monthList[0] will be largest
    – summer
    Commented Oct 11, 2019 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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .