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
Required: At least onetable.fieldASC or DESCThe direction, written after the column inside the same quotes.
Required: NoASCis the default"RELEVANCE"Not a column: the ranking of a
Required: NoMATCHESsearch in the same statement, best match first."RELEVANCE ASC"reverses it
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
| Error | What happened | How to recover |
|---|---|---|
HTQL-2001 SORT direction must be ASC or DESC | The word after the column is not ASC or DESC | Use ASC or DESC |
HTQL-2001 | The column does not exist on the table. This also happens when the direction is written as its own term | Check 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, andDESCis 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.