--- title: "create_sort_key" slug: "dev-database-reference-sql-functions-utility-create-sort-key" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-utility-create-sort-key" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # create_sort_key *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Session and utility functions](/develop/docs/dev-database-reference-sql-functions-utility) › Sorting.* Builds a `BLOB` from one or more values, each with a sort specifier such as `'ASC NULLS LAST'`, so that comparing the `BLOB`s byte by byte orders the rows as `ORDER BY` with those keys would. One value then stands for a sort order over several columns. ## Syntax ``` create_sort_key(key1, specifier1) create_sort_key(key1, specifier1, key2, specifier2, ...) ``` | Argument | Type | Description | | --- | --- | --- | | `key1`, `key2`, … | Any type | The values to sort by, most significant first. | | `specifier1`, `specifier2`, … | `VARCHAR` constant | How to sort each key: `'ASC NULLS FIRST'`, `'ASC NULLS LAST'`, `'DESC NULLS FIRST'` or `'DESC NULLS LAST'`. It must start with `ASC` or `DESC` and end with `NULLS FIRST` or `NULLS LAST`. | **Returns:** `BLOB`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_utility_create_sort_key`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of six tasks with a priority and a name. Task 5 has no priority and the last row has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_utility_create_sort_key"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.tasks" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, priority BIGINT, name VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, 2, 'deploy'), " "(2, 1, 'backup'), " "(3, 2, 'audit'), " "(4, -1, 'archive'), " "(5, NULL, 'cleanup'), " "(6, NULL, NULL)") ``` ``` [] [] [(6,)] ``` ## What it does `create_sort_key` encodes each key so that a byte-by-byte comparison of the results follows its specifier, and joins the encoded keys, most significant first. For a single `BIGINT` key with `'ASC NULLS LAST'`, the keys of -1, 0, 1, 2 and 5 compare in that order, and the key of a `NULL` compares after all of them. Use it where one value has to carry a sort order over several columns, for example to keep the sort key of the last row you returned and compare the next rows with it. ## NULLs and edge cases | Input | Result | | --- | --- | | A `NULL` key | A key that sorts first or last, as the specifier's `NULLS FIRST` or `NULLS LAST` says | | A `NULL` specifier | Error: `sort_specifier cannot be NULL` | | A specifier from a column | Error: `sort_specifier must be a constant value - but got s` (for a column `s`) | | `'DESC'` or `'ASC'` without `NULLS ...` | Error: `create_sort_key modifier must end with either NULLS FIRST or NULLS LAST` | | A specifier that doesn't start with `ASC` or `DESC` | Error: `create_sort_key modifier must start with either ASC or DESC` | | One argument, or a key without a specifier | Error: `Arguments to create_sort_key must be [key1, sort_specifier1, key2, sort_specifier2, ...]` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-utility-create-sort-key#set-up-the-examples): ``` run(f"SELECT id, priority, create_sort_key(priority, 'ASC NULLS LAST'), create_sort_key(priority, 'ASC NULLS FIRST') FROM {TABLE} ORDER BY id LIMIT 10") run("SELECT create_sort_key(1, NULL)") run(f"SELECT create_sort_key(priority, name) FROM {TABLE} LIMIT 1") run("SELECT create_sort_key(42, 'DESC')") run("SELECT create_sort_key(42, 'SIDEWAYS')") run("SELECT create_sort_key('a', 'ASC NULLS LAST', 1)") ``` ``` [(1, 2, b'\x01\x80\x00\x00\x00\x00\x00\x00\x02', b'\x02\x80\x00\x00\x00\x00\x00\x00\x02'), (2, 1, b'\x01\x80\x00\x00\x00\x00\x00\x00\x01', b'\x02\x80\x00\x00\x00\x00\x00\x00\x01'), (3, 2, b'\x01\x80\x00\x00\x00\x00\x00\x00\x02', b'\x02\x80\x00\x00\x00\x00\x00\x00\x02'), (4, -1, b'\x01\x7f\xff\xff\xff\xff\xff\xff\xff', b'\x02\x7f\xff\xff\xff\xff\xff\xff\xff'), (5, None, b'\x02\x00\x00\x00\x06\x00\x00\x00\x00', b'\x01\x00\x00\x00\x06\x00\x00\x00\x00'), (6, None, b'\x02\x00\x00\x00\x06\x00\x00\x00\x00', b'\x01\x00\x00\x00\x06\x00\x00\x00\x00')] InternalError: Binder Error: sort_specifier cannot be NULL InternalError: Binder Error: sort_specifier must be a constant value - but got name InternalError: Binder Error: create_sort_key modifier must end with either NULLS FIRST or NULLS LAST InternalError: Binder Error: create_sort_key modifier must start with either ASC or DESC InternalError: Binder Error: Arguments to create_sort_key must be [key1, sort_specifier1, key2, sort_specifier2, ...] ``` ## Examples The tasks by priority, highest first with the tasks without a priority last, then by name. The second query sorts on the columns directly, for comparison: ``` run(f"SELECT id, priority, name FROM {TABLE} " "ORDER BY create_sort_key(priority, 'DESC NULLS LAST', name, 'ASC NULLS LAST') LIMIT 10") run(f"SELECT id, priority, name FROM {TABLE} ORDER BY priority DESC NULLS LAST, name ASC NULLS LAST LIMIT 10") ``` ``` [(3, 2, 'audit'), (1, 2, 'deploy'), (2, 1, 'backup'), (4, -1, 'archive'), (5, None, 'cleanup'), (6, None, None)] [(3, 2, 'audit'), (1, 2, 'deploy'), (2, 1, 'backup'), (4, -1, 'archive'), (5, None, 'cleanup'), (6, None, None)] ``` The tasks that come after task 3 in that order: compare each row's key with the key of task 3, built from its values (priority 2, name `'audit'`): ``` run(f"SELECT id, priority, name FROM {TABLE} " "WHERE create_sort_key(priority, 'DESC NULLS LAST', name, 'ASC NULLS LAST') " "> create_sort_key(2::BIGINT, 'DESC NULLS LAST', 'audit'::VARCHAR, 'ASC NULLS LAST') " "ORDER BY create_sort_key(priority, 'DESC NULLS LAST', name, 'ASC NULLS LAST') LIMIT 10") ``` ``` [(1, 2, 'deploy'), (2, 1, 'backup'), (4, -1, 'archive'), (5, None, 'cleanup'), (6, None, None)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so `create_sort_key()` is always computed by the Query Engine: - **In the `SELECT` list or `ORDER BY`,** the scan reads the key columns and the Query Engine builds the key for each row. - **In `WHERE`,** the scan sends the key columns for every row, and a `FILTER` step in the Query Engine builds the key and keeps the rows that match. No rows are skipped in storage. The plan of the `WHERE` example shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} " "WHERE create_sort_key(priority, 'DESC NULLS LAST', name, 'ASC NULLS LAST') " "> create_sort_key(2::BIGINT, 'DESC NULLS LAST', 'audit'::VARCHAR, 'ASC NULLS LAST')") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #2 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (create_sort_key(priority,│ │ 'DESC NULLS LAST', name, │ │ 'ASC NULLS LAST') > '\x01 │ │ \x7F\xFF\xFF\xFF\xFF\xFF │ │ \xFF\xFD\x01bveju\x00': │ │ :BLOB) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ priority │ │ name │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Sort on the columns when that's all you need.** `ORDER BY priority DESC NULLS LAST, name` is the plain way to get the order; build a key only when you need the order as one value. - **Narrow the rows first with a pushed-down condition.** A comparison of a raw column with a constant, such as `priority >= 1`, is evaluated in VAST DataBase, so the key is built only for the rows that pass it. Its plan has no `FILTER` step: ``` plan(f"SELECT id FROM {TABLE} WHERE priority >= 1") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` ## Limitations and errors - **Every key needs a full specifier,** with both the direction and the place of `NULL`s: `'ASC NULLS LAST'`, not `'ASC'`. A shorter one fails with `create_sort_key modifier must end with either NULLS FIRST or NULLS LAST`. - **Specifiers are constants.** A specifier taken from a column fails with `sort_specifier must be a constant value`. - **The key is a `BLOB`.** Compare keys with each other, not with the original values, and don't parse the bytes: build the key you compare with from values, as in the second example. - **`ORDER BY` needs a `LIMIT`**, as for any `ORDER BY` in the VAST Query Engine. Without one, the query fails with `The operator 'ORDER_BY' type=1 is currently not supported for this usage.` ``` run(f"SELECT id FROM {TABLE} ORDER BY create_sort_key(priority, 'ASC NULLS LAST')") ``` ``` InternalError: The operator 'ORDER_BY' type=1 is currently not supported for this usage. ``` ## Similar functions | Function or clause | Does | | --- | --- | | `ORDER BY a DESC NULLS LAST, b ASC ... LIMIT n` | Sorts the rows by several keys, without building a value | | `create_sort_key(a, 'DESC NULLS LAST', b, 'ASC NULLS LAST')` | The same order, as one `BLOB` per row | | [list_sort](/develop/docs/dev-database-reference-sql-functions-lists-list-sort) | Sorts the elements of a list, not rows | ``` run(f"SELECT id FROM {TABLE} ORDER BY priority DESC NULLS LAST, name ASC NULLS LAST LIMIT 10") run(f"SELECT id FROM {TABLE} ORDER BY create_sort_key(priority, 'DESC NULLS LAST', name, 'ASC NULLS LAST') LIMIT 10") run("SELECT list_sort([3, 1, 2])") ``` ``` [(3,), (1,), (2,), (4,), (5,), (6,)] [(3,), (1,), (2,), (4,), (5,), (6,)] [([1, 2, 3],)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [SELECT](/develop/docs/dev-database-reference-sql-select): `ORDER BY` and `LIMIT`. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.