[SalesForce] How to extract the MAX number from the query

The following SOQL gives me the list of max rows with quantity/product name
but I'm only looking to extract the maximum quantity out of all the records.

Do I need to loop to get that?

   SELECT max(Quantity__c) qty,  product_name__c FROM Asset_line_item__c 
     where Id = 'xxxxx'
    group by product_name__c

I have tried with AggregateResult.

AggregateResult[] result =  [max(Quantity__c) qty,  
              product_name__c FROM Asset_line_item__c where Id = 'xxxxx' 
              group by product_name__c];

Best Answer

You should be able to just use get('qty') or even get('expr0').

for (AggregateResult aggregate : [
    SELECT MAX(Quantity__c) qty,  Product_Name__c FROM Asset_Line_Item__c 
    WHERE ... GROUP BY Product_Name__c
]) system.debug(aggregate.get('Product_Name__c') + ' - ' + aggregate.get('qty');

Or if you just want the highest max and felt compelled to use aggregation:

Decimal max;
for (AggregateResult aggregate : [
    SELECT MAX(Quantity__c) qty,  Product_Name__c FROM Asset_Line_Item__c 
    WHERE ...
    GROUP BY Product_Name__c
    ORDER BY MAX(Quantity__c) DESC
]){
    max = (Decimal)aggregate.get('qty');
    break;
}

Or, as noted in the comments, just get the single highest value with no aggregation at all.

Decimal max = [
    SELECT Quantity__c FROM Asset_Line_Item__c
    ORDER BY Quantity__c DESC LIMIT 1
].Quantity;
Related Topic