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.
How to select all documents with a max value on a field where the field is calculated at runtime?
Hello @Libin_Zhou ,
Welcome to The MongoDB Community Forums!
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?