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
Required: YesorderSORTBY
Required: Yeskeys
Required: NoorderUNIQUE
Required: Noby_col
Required: Noexactly_once
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
SORT | list | The list in the new order. Records keep all their fields | items.SORT("price desc") |
SORTBY | list | The list ordered by the keys | names.SORTBY(scores) |
UNIQUE | list | The list without repeats, in the order the values first appear | tags.UNIQUE() |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2001 | The value in front of the function is not a list, or the keys of SORTBY are not the same length | Pass 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. SORTBYkeys are numbers. Its key list must be the same length as the list, or the call is refused.UNIQUEcompares values by their type as well as their value:1and1.0are 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.