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.