SORT

Put the rows in the order your code or your reader expects.

Syntax

Write each sort term as one quoted text: the column, then an optional direction.

SORT("column")
SORT("column DESC", "other_column ASC")

Parameters and arguments

  • "column"

    The field to order by, written as the answer names it. In a join, write table.field

    Required: At least one
  • ASC or DESC

    The direction, written after the column inside the same quotes. ASC is the default

    Required: No
  • "RELEVANCE"

    Not a column: the ranking of a MATCHES search in the same statement, best match first. "RELEVANCE ASC" reverses it

    Required: No

List several terms to break ties: the rows are ordered by the first, then the second, and so on. Each term has its own direction.

Examples

The last three navigation links by identifier, in descending order.

Return type and behaviour

SORT does not return anything itself. It sets the order of the rows the statement returns.

Text columns are ordered by the database's collation, so letter case does not decide the order: calLinkApple comes before NavCompany0. A number column is ordered as a number: 10 comes after 9.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-2001 SORT direction must be ASC or DESCThe word after the column is not ASC or DESCUse ASC or DESC
HTQL-2001The column does not exist on the table. This also happens when the direction is written as its own termCheck the column's name, and keep the direction inside the same quotes
Placement and context

Inside LOOKUP, SORT is one of the function's arguments: the rows are ordered before TAKE keeps its share, wherever each is written among the arguments. Inside NEST, SORT orders each parent's children. After GET or in a FOR body, SORT is a step that runs where it is written: written first it is part of the database query; after a LET it can order by the value just bound, and after a RETURN by a field of the answers. See GET.

Limitations
  • Not supported: sorting by a value instead of a column, such as an alias with a dot (SORT(e.id)) or a computed expression. SORT takes the names of columns, quoted.
  • Put the column and the direction in one text. SORT("id", "DESC") is read as two columns, and DESC is not a column.
  • A second SORT in the same LOOKUP replaces the first. List every term in one SORT.
  • A LOOKUP written inside RETURN, rather than kept with LET, does not apply its SORT.
Permissions and security

SORT grants no access of its own.

Best practices
  • Sort before you page with TAKE, so each page continues from the last.
  • Add a second term, such as the identifier, so rows with equal values keep a steady order.
  • Sort in the statement that reads the data.