I have the following Query

[Select Supplier__c, Avg(Total_Sales__c) from Submittal group by Supplier__c order by Purchase_Date__c DESC]

The issue is that I only want to include the latest 10 records in my average total sales calculation. Is such a thing possible? It must be done with SOQL, not Apex as querying all the submittals will be way over the limit for query rows returned.

  • You'd likely need a custom rollup (implemented via Apex Trigger) to make this requirement work. – Adrian Larson May 29 '19 at 15:00

If you want the last 10 avgs for each supplier then you are going to have to use something like ROW_NUMBER(). You can partition by supplier with purchase_date descending where row num < 10. That should give you a start on getting what you need.

| improve this answer | |
  • would this not only give me the 10 suppliers with the most recent purchase dates? I want all suppliers, just the last 10 Total_Sales__c averaged from each – Hans Donkersloot May 29 '19 at 14:51
  • Updated my answer. – Jason Hanshaw May 29 '19 at 15:00
  • 2
    Does SOQL support the ROW_NUMBER() function? A google search isn't coming up with any documentation, could you post an example of how you'd use it in SOQL? – Hans Donkersloot May 29 '19 at 15:08

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.