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</pre>

    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.

Next page: Specifics of connecting to the virtual balances table