Sorting functions

Put a list in order, or keep only its distinct values, inside a statement.

Syntax

These are array functions, written as a method on a variable that holds the list: after LET items = [...], write items.NAME(...), or order.lines.NAME(...) for a list inside a record. The list is what is written before the function's name, so it is not one of the arguments in the brackets. Written with the list first inside brackets, SORT(items, ...), the name is the SORT or TAKE clause of a statement, not this function.

SORT(order)
SORTBY(keys, [order])
UNIQUE([by_col], [exactly_once])

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • SORT

    order

    Required: Yes
  • SORTBY

    keys

    Required: Yes
  • order

    Required: No
  • UNIQUE

    by_col

    Required: No
  • exactly_once

    Required: No

Examples

An order's lines held as a list of records: the lines in order of price, and in order of quantity then price.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
SORTlistThe list in the new order. Records keep all their fieldsitems.SORT("price desc")
SORTBYlistThe list ordered by the keysnames.SORTBY(scores)
UNIQUElistThe list without repeats, in the order the values first appeartags.UNIQUE()
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2001The value in front of the function is not a list, or the keys of SORTBY are not the same lengthPass a list, and keys with one value for each item
Limitations
  • The list must be a list. A value that is not a list is refused with HTQL-2001.
  • SORTBY keys are numbers. Its key list must be the same length as the list, or the call is refused.
  • UNIQUE compares values by their type as well as their value: 1 and 1.0 are different.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Hold a list once with LET and call these functions on it, so the data is written in one place.
  • Use UNIQUE before LENGTH to count distinct values.