Ordering query results

Scope: managed applications, mobile applications, and ordinary applications.

1. Employ the ORDER BY caluse if a) the query results will be displayed to the user, b) the output of the algorithm that will process the query results depends on the row order. Otherwise, without the ORDER BY clause, the order of the rows is unpredictable.

Here are examples of some common issues that you might face even if you run the same query against the same database, but at different times:

  • Different row ordering.
  • Different column ordering.
  • Different document's record ordering.*

The chance to get a result that differs from the previous query result is increasing in the following cases: 

  • The infobase has been migrated to another database.
  • The DBMS version has been updated.
  • The DBMS settings have been changed.

* Note. Order query results by the document's records only when this is a part of the recording algorithm. For example, when you use FIFO inventory method. Otherwise, keep the records unordered to reduce the database load and save the bandwidth.

2. When you order a query result by fields that might contain NULL, take into account that the sorting priority of NULL varies in different DBMS.

Incorrect:

SELECT
  CatalogProducts.Ref AS ProductRef,
  InventoryBalance.QuantityBalance AS QuantityBalance
FROM
  Catalog.Products AS CatalogProducts
    LEFT JOIN AccumulationRegister.Inventory.Balance AS InventoryBalance
    BY (InventoryBalance.Products = CatalogProducts.Ref)

ORDER BY
  QuantityBalance

Correct:

SELECT
  CatalogProducts.Ref AS ProductRef,
  ISNULL(InventoryBalance.QuantityBalance, 0) AS QuantityBalance
FROM
  Catalog.Products AS CatalogProducts
    LEFT JOIN AccumulationRegister.Inventory.Balance AS InventoryBalance
    BY (InventoryBalance.Products = CatalogProducts.Ref)

ORDER BY
  QuantityBalance

See also: Features of operating with different DBMS (https://support.1ci.com/hc/en-us/sections/360004697134-Appendix-8-Features-of-operating-with-different-DBMS)

3. If you need to display a query result to users, follow the instructions:

  • Order the result by the fields that contain primitive data types.
  • To order the result by fields that contain reference data type, convert the references into strings, and then order the rows by the fields that contain strings.

Otherwise, the ordered query result might look random to the users.

See also: Sorting value tables

4. Don't use ORDER BY in the following cases:

  • The algorithm that will process the query result does not require rows to be ordered.
  • Users are not supposed to see the query result.
  • The query result always contains only one row.

In these cases, using the ORDER BY clause is unnecessary and will decrease the query execution time.

DISTINCT clause

If the query text contains the DISTINCT clause, the query result must be ordered only by the fields that come under the SELECT clause. 

This recommendation is driven by the 1C:Enterprise query language design: the ordering fields are implicitly included in the selection fields. As a result, some strings with the same value might appear in the query result. 

AUTOORDER clause

3. Do not use the TOP clause and the AUTOORDER clause in the same query.

In most cases, AUTOORDER is not recommended to employ, since you cannot specify which fields are selected for ordering. The only reasonable case to use autoordering is when the resulting order is not important, but must be the same for any DBMS. 

When you embed AUTOORDER in the query text, please leave a comment with the reason why you used this clause.