How to get MAX value using SOQL when there are more than 50,000 rows

Question

I would like to run this query:

SELECT MAX(Auto_Number__c) MaxNumber FROM Opportunity

But I am getting this error:

System.LimitException: Too many query rows: 50001

Because there are more than a 100,000 records.

Would adding a ORDER BY and LIMIT to the query still ensure I get the MAX?

SELECT MAX(Auto_Number__c) MaxNumber FROM Opportunity ORDER BY Auto_Number__c DESC LIMIT 1

But this give me:

Ordered field must be grouped or aggregated: Auto_Number__c (129:16)

Answer 1

Take out the MAX from your query:

SELECT Auto_Number__c FROM Opportunity ORDER BY Auto_Number__c DESC LIMIT 1