Sort an array in ascending or descending order.
SORT_ARRAY(array [, asc])## Overview Returns a new array with the elements sorted in ascending or descending order. SORT_ARRAY is the direction-aware counterpart of ARRAY_SORT; when the asc argument is omitted or true, the two functions produce the same result. Typical uses include producing descending orderings without reversing a sorted array, imposing deterministic order on per-row collections before slicing, and normalising arrays for equality comparison. ## Behavior - Returns an ARRAY<T> of the same length and element type as the input. - When asc is true (default), sorts ascending; when false, sorts descending. - Uses the element type's natural ordering. Strings use the engine's default collation; numeric types follow IEEE 754 semantics. - NULL placement: ascending sort places NULLs at the end; descending sort places NULLs at the beginning. - Sort is stable for equal elements. - Runs in O(n log n). The input is never mutated. - Works only on comparable element types. ## Null and empty handling - NULL input array returns NULL. - Empty input array returns an empty array. - NULL elements are preserved with placement depending on the asc flag. - An all-NULL array returns an array of the same length containing only NULLs (placement is irrelevant). ## Compatibility - Matches the array/map SQL convention for SORT_ARRAY. ARRAY_SORT is the ascending-only variant with the same NULLS LAST placement. - For custom orderings beyond ascending or descending, use an EXPLODE and ORDER BY pipeline or a higher-order SORT_ARRAY variant with a comparator (where supported).
| Name | Type | Description |
|---|---|---|
array | Specifies the input array. Elements must be of a comparable type. | |
asc | Indicates whether to sort in ascending order. Defaults to true; pass false for descending. |
-- Ascending sort (default)
SELECT SORT_ARRAY(ARRAY[3, 1, 4, 1, 5]); -- [1, 1, 3, 4, 5]-- Descending sort
SELECT SORT_ARRAY(ARRAY[3, 1, 4, 1, 5], false); -- [5, 4, 3, 1, 1]-- Sort strings ascending
SELECT SORT_ARRAY(ARRAY['banana', 'apple', 'cherry']); -- ['apple', 'banana', 'cherry']-- NULLs placed at end for ascending
SELECT SORT_ARRAY(ARRAY[3, CAST(NULL AS INT), 1, 2]); -- [1, 2, 3, NULL]-- NULLs placed at beginning for descending
SELECT SORT_ARRAY(ARRAY[3, CAST(NULL AS INT), 1, 2], false); -- [NULL, 3, 2, 1]-- Rank a per-session latency array descending and keep the top three
SELECT session_id, SLICE(SORT_ARRAY(page_load_ms, false), 1, 3) AS top_three_slowest
FROM analytics.events.user_sessions;