How to select all documents with a max value on a field where the field is calculated at runtime?

Working with Data
1

For example: my SQL query can be SELECT * FROM (SELECT ID, SUM(revenue) as total_revenue FROM TABLE2 GROUP BY ID) as TABLE3, (SELECT MAX(revenue) as max_revenue FROM TABLE3) WHERE total_revenue = max_revenue; I was trying to lookup online but did not find any solution that targets a complex nested query like this.

2

Hello @Libin_Zhou ,

Welcome to The MongoDB Community Forums! :wave:

To understand your use case better, please provide more details, such as:

  • MongoDB Version being used
  • Your requirements of this scenario
  • Collections being used
  • Some sample documents with respect to the collections
  • Expected output document/s
  • Any queries that you worked on to achieve the required results?

Regards,
Tarun