array_sort function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns array sorted according to func.

Syntax 

array_sort(array, 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]