array_sort
function
Applies to: Databricks SQL Databricks Runtime
Returns array
sorted according to func
.
Arguments
array
: An expression that evaluates to an array.func
: A lambda function defining the sort order.
Returns
The result type matches the type of array
.
If func
is omitted, the array is sorted in ascending order.
If func
is provided it takes two arguments representing two elements of the array.
The function must return -1, 0, or 1 depending on whether the first element is less than, equal to, or greater than the second element.
If the func
returns other values (including NULL), array_sort
fails and raises an error.
NULL elements are placed at the end of the returned array.
Examples
> SELECT array_sort(array(5, 6, 1),
(left, right) -> CASE WHEN left < right THEN -1
WHEN left > right THEN 1 ELSE 0 END);
[1,5,6]
> SELECT array_sort(array('bc', 'ab', 'dc'),
(left, right) -> CASE WHEN left IS NULL and right IS NULL THEN 0
WHEN left IS NULL THEN -1
WHEN right IS NULL THEN 1
WHEN left < right THEN 1
WHEN left > right THEN -1 ELSE 0 END);
[dc,bc,ab]
> SELECT array_sort(array('b', 'd', null, 'c', 'a'));
[a,b,c,d,NULL]