list_sort

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Sort a list. Returns the elements of a list sorted, in ascending order by default. Optional arguments choose descending order and where NULL elements go. array_sort is the same function.

Syntax

list_sort(l)
list_sort(l, order)
list_sort(l, order, nulls)
array_sort(l)
array_sort(l, order)
array_sort(l, order, nulls)

Returns: a list of the same type as l.

Aliases: array_sort.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_lists_list_sort. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), 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 five rows of lists: numbers, words and measurements, and a sort direction. Row 3 has empty lists and row 4 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_lists_list_sort"'

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, "<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}.samples"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, nums BIGINT[], words VARCHAR[], vals DOUBLE[], direction VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], ['a', 'b', 'c'], [0.5, 2.5, -1.0], 'ASC'), "
    "(2, [3, NULL, 3], ['b', NULL, 'B'], [2.0, NULL], 'DESC'), "
    "(3, [], [], [], 'ASC'), "
    "(4, NULL, NULL, NULL, NULL), "
    "(5, [-5, 0, -5, 10], ['\u00e9', 'Zo\u00eb', '\U0001F1EB\U0001F1F7', ''], [1e308, 1e308, -1e-308], 'DESC')")

[]
[]
[(5,)]

What it does

list_sort(l) returns the elements of l from smallest to largest. Duplicates are kept: [-5, 0, -5, 10] sorts to [-5, -5, 0, 10].

• order is 'ASC' or 'DESC'. list_sort(l, 'DESC') is the same as list_reverse_sort(l).
• nulls places the NULL elements. They go last by default, in both orders: list_sort([2, NULL, 1], 'DESC') is [2, 1, NULL]. Pass 'NULLS FIRST' to put them first. nulls is the third argument, so give order with it.

How values compare:

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT list_sort(NULL), list_sort([]), list_sort([NULL, NULL]), list_sort([2, NULL, 1]), list_sort([2, NULL, 1], 'DESC')")
run("SELECT list_sort([2, NULL, 1], 'ASC', 'NULLS FIRST'), list_sort([2, NULL, 1], 'DESC', 'NULLS FIRST'), "
    "list_sort([2, NULL, 1], 'desc'), list_sort([2, 1], NULL)")
run("SELECT list_sort(['\u00e9', 'e', 'f', 'Z'])")
run("SELECT list_sort([2, NULL, 1], 'NULLS FIRST')")
run("SELECT list_sort([2, NULL, 1], 'ASC', 'NULLS MIDDLE')")
run("SELECT list_sort([2, 1], 1)")
run("SELECT list_sort('abc')")

[(None, [], [None, None], [1, 2, None], [2, 1, None])]
[([None, 1, 2], [None, 2, 1], [2, 1, None], None)]
[(['Z', 'e', 'f', 'é'],)]
InternalError: Not implemented Error: Enum value: unrecognized value "NULLS FIRST" for enum "OrderType"
InternalError: Not implemented Error: Enum value: unrecognized value "NULLS MIDDLE" for enum "OrderByNullType"
InternalError: Binder Error: No function matches the given name and argument types 'list_sort(INTEGER[], INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'list_sort(STRING_LITERAL)'. You might need to add explicit type casts.

The comparison rules from What it does:

run("SELECT list_sort(['NaN'::DOUBLE, 1.0, -'inf'::DOUBLE, 'inf'::DOUBLE, NULL]), list_sort(['b', 'A', 'a', 'B'])")
run("SELECT list_sort([true, false, NULL]), list_sort([DATE '2026-10-17', DATE '2026-10-16']), "
    "list_sort([[2], [1, 9], [1], []])")

[([-inf, 1.0, inf, nan, None], ['A', 'B', 'a', 'b'])]
[([False, True, None], [datetime.date(2026, 10, 16), datetime.date(2026, 10, 17)], [[], [1], [1, 9], [2]])]

Examples

Each row's numbers in ascending order, and in descending order with NULLs first; and the words sorted with array_sort:

run(f"SELECT id, list_sort(nums), list_sort(nums, 'DESC', 'NULLS FIRST'), array_sort(words) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [1, 2, 3], [3, 2, 1], ['a', 'b', 'c']), (2, [3, 3, None], [None, 3, 3], ['B', 'b', None]), (3, [], [], []), (4, None, None, None), (5, [-5, -5, 0, 10], [10, 0, -5, -5], ['', 'Zoë', 'é', '🇫🇷'])]

Compare lists regardless of their order: sort both sides. Row 1 matches whatever order its elements were written in:

run(f"SELECT id FROM {TABLE} WHERE list_sort(nums) = [1, 2, 3] ORDER BY id LIMIT 10")

[(1,)]

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). The scan reads a list column whole, and list_sort() always runs in the Query Engine:

• In the SELECT list, a projection step sorts each row's list after the scan.
• In WHERE, the scan sends the list for every row, and a FILTER step sorts it and keeps the rows that match. No rows are skipped in storage.

The plan of the comparison example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE list_sort(nums) = [1, 2, 3]")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (list_sort(nums) = [1, 2, │
│             3])           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            nums           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Add a condition on a plain column. A comparison of a column with a constant, such as id > 1, is evaluated in VAST DataBase, so list_sort() runs only on the rows that pass it. The plan for id > 1 alone has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE id > 1")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Sort when you write. If queries compare or read lists in sorted order, store them sorted, so no query has to sort them again.

Limitations and errors

• order and nulls must be constants. Taking them from a column fails with Invalid Input Error: Sorting order must be a constant. Use a CASE expression with a constant in each branch.
• Only the accepted words. Another order or nulls value fails with Not implemented Error: Enum value: unrecognized value .... A number instead of a word fails with No function matches the given name and argument types.
• No custom comparison. A lambda as the second argument, as in list_sort(l, (a, b) -> b - a), fails with This scalar function does not support lambdas!. To sort one list by the values of another, use list_grade_up with list_select.

run(f"SELECT id, list_sort(nums, direction) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT list_sort([3, 1, 2], (a, b) -> b - a)")

InternalError: Invalid Input Error: Sorting order must be a constant
InternalError: Binder Error: failed to bind function, either: This scalar function does not support lambdas!

The CASE form runs:

run(f"SELECT id, CASE WHEN direction = 'DESC' THEN list_sort(nums, 'DESC') ELSE list_sort(nums) END "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [1, 2, 3]), (2, [3, 3, None]), (3, []), (4, None), (5, [10, 0, -5, -5])]

Similar functions

run("SELECT list_sort(l), list_sort(l, 'DESC'), list_reverse_sort(l), list_reverse(l), list_grade_up(l) "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[([1, 3, 3, 10, None], [10, 3, 3, 1, None], [10, 3, 3, 1, None], [10, 3, None, 1, 3], [2, 1, 4, 5, 3])]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• List, struct and map functions: all the list functions.
• list_distinct: its result has no fixed order; sort it with list_sort.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.