Sorting in nested queries
The query language supports sorting in nested queries. You can use sorting in a nested query if the query has a limitation on the number of retrieved records. This means that a nested query may only include sorting if it includes the TOP statement.
For example, let us consider a query that gets sales volumes for the five most expensive products.
SELECT NestedQuery.Ref, SalesTurnovers.QuantityTurnover, SalesTurnovers.AmountTurnover FROM (SELECT TOP 5 Items.Ref AS Ref FROM Catalog.Items AS Items ORDER BY Items.PurchasePrice DESC) AS NestedQuery LEFT JOIN AccumulationRegister.Sales.Turnovers(,,, ITEM IN (SELECT TOP 5 Items.Ref AS Ref FROM Catalog.Items AS Items ORDER BY Items.PurchasePrice DESC)) AS SalesTurnovers BY NestedQuery.Ref = SalesTurnovers.Items
In this query, the nested query gets the five products with the highest value in the PurchasePrice field, and in the main query the retrieved products are linked to the sales register that provides the sales data. To limit the calculation of turnovers, the list of the most expensive products is also passed to the condition of the AccumulationRegister.Sales.Turnovers table.