A Look at DuckDB’s LIST_SORT() Function

Posted on by Ian

DuckDB has a list_sort() function that does exactly what its name promises; sorts lists.

While the easiest way to use this function is to simply pass a list, we can also pass other arguments to fine-tune the results.

Syntax

The syntax goes like this:

list_sort(ANY[]) -> ANY[]
list_sort(ANY[], VARCHAR) -> ANY[]
list_sort(ANY[], VARCHAR, VARCHAR) -> ANY[]
  • The first form allows us to simply pass the list to sort using the system’s default sort settings.
  • The second form allows us to specify whether to sort in ascending or descending order.
  • The third form allows us to also specify how NULL values are sorted.

There’s also an array_sort() function, which is an alias of list_sort(). We can use the same syntax with array_sort() to get the same result.

Examples

Here’s a list of numbers:

SELECT list_sort( [3, 5, 1] );

Result:

[1, 3, 5]

Here’s a list with strings:

SELECT list_sort( ['apple', 'kiwi', 'banana'] );

Result:

[apple, banana, kiwi]

We can sort date/time values too:

SELECT list_sort([ 
    date '2025-01-01',
    date '2020-01-01',
    date '2021-01-01',
]);

Result:

[2020-01-01, 2021-01-01, 2025-01-01]

We can even sort nested lists:

SELECT list_sort([
    ['banana', 'kiwi', 'apple'],
    ['apple', 'kiwi', 'banana'],
    ['kiwi', 'apple', 'banana']
    ]);

Result:

[[apple, kiwi, banana], [banana, kiwi, apple], [kiwi, apple, banana]]

Duplicate Values

As you might expect, any duplicate values in the list are returned next to each other:

SELECT list_sort( ['apple', 'kiwi', 'banana', 'kiwi', 'apple'] );

Result:

[apple, apple, banana, kiwi, kiwi]

Specifying Ascending or Descending Order

As mentioned, we can pass a second argument in order to specify whether the list should be sorted in ascending or descending order:

SELECT 
    list_sort(['apple', 'kiwi', NULL, 'banana', 'apple'], 'ASC') AS ascending,
    list_sort(['apple', 'kiwi', NULL, 'banana', 'apple'], 'DESC') AS descending;

Result:

+------------------------------------+------------------------------------+
|             ascending              |             descending             |
+------------------------------------+------------------------------------+
| [apple, apple, banana, kiwi, NULL] | [kiwi, banana, apple, apple, NULL] |
+------------------------------------+------------------------------------+

By default, DuckDB sorts in ascending order (ASC), but we also have the ability to change this by setting the default_order configuration option. For example:

SET default_order = 'DESC';

After doing that, all subsequent queries will be sorted in descending order, unless you explicitly specify a different sort order in your query.

Dealing with NULL Values

We can pass a third argument in order to specify how NULL values are sorted.

When it comes to the default sorting for NULL values, the DuckDB documentation appears to have conflicting information. On its sorting lists page it states that NULL values are placed first, but on its page about the ORDER BY clause, it states that NULL values come last.

My experience with running this across several systems is that NULL values come last by default, including with lists. We can see this in the previous example.

Regardless, DuckDB does provide us with the ability to change this with the default_null_order configuration option.

Here’s an example that demonstrates the various options we have when using the third argument:

SELECT 
    list_sort(['apple', NULL, 'banana'], 'ASC', 'NULLS_FIRST') AS 'ASC, NULLS_FIRST',
    list_sort(['apple', NULL, 'banana'], 'ASC', 'NULLS_LAST') AS 'ASC, NULLS_LAST',
    list_sort(['apple', NULL, 'banana'], 'DESC', 'NULLS_FIRST') AS 'DESC, NULLS_FIRST',
    list_sort(['apple', NULL, 'banana'], 'DESC', 'NULLS_LAST') AS 'DESC, NULLS_LAST';

Result:

+-----------------------+-----------------------+-----------------------+-----------------------+
|   ASC, NULLS_FIRST    |    ASC, NULLS_LAST    |   DESC, NULLS_FIRST   |   DESC, NULLS_LAST    |
+-----------------------+-----------------------+-----------------------+-----------------------+
| [NULL, apple, banana] | [apple, banana, NULL] | [NULL, banana, apple] | [banana, apple, NULL] |
+-----------------------+-----------------------+-----------------------+-----------------------+

So we have the option of specifying exactly where NULL values will be placed in relation to other values, depending on whether we’re using ascending order or descending.

Checking the Default Order

We can use the following query to check the default order that our system uses:

SELECT value AS setting
FROM duckdb_settings()
WHERE name IN ('default_order', 'default_null_order');

Sample output:

+------------+
|  setting   |
+------------+
| asc        |
| nulls_last |
+------------+

Setting the Default Order

We can change the above settings like this:

SET default_order = 'DESC';
SET default_null_order = 'NULLS_FIRST';

An Alias: The array_sort() Function

DuckDB also offers an array_sort() function, which is an alias of list_sort(). Therefore, we can use either one to get the same result. We can replace list_sort() in the above examples with array_sort(). For example:

SELECT 
    array_sort(['apple', NULL, 'banana'], 'ASC', 'NULLS_FIRST') AS 'ASC, NULLS_FIRST',
    array_sort(['apple', NULL, 'banana'], 'ASC', 'NULLS_LAST') AS 'ASC, NULLS_LAST',
    array_sort(['apple', NULL, 'banana'], 'DESC', 'NULLS_FIRST') AS 'DESC, NULLS_FIRST',
    array_sort(['apple', NULL, 'banana'], 'DESC', 'NULLS_LAST') AS 'DESC, NULLS_LAST';

Result:

+-----------------------+-----------------------+-----------------------+-----------------------+
|   ASC, NULLS_FIRST    |    ASC, NULLS_LAST    |   DESC, NULLS_FIRST   |   DESC, NULLS_LAST    |
+-----------------------+-----------------------+-----------------------+-----------------------+
| [NULL, apple, banana] | [apple, banana, NULL] | [NULL, banana, apple] | [banana, apple, NULL] |
+-----------------------+-----------------------+-----------------------+-----------------------+