While it’s true that DuckDB has a list_sort() function that allows us to sort lists, it’s also true that there’s a list_reverse_sort() function that sorts lists in reverse order.

Syntax

The syntax goes like this:

list_reverse_sort(ANY[]) -> ANY[]
list_reverse_sort(ANY[], VARCHAR) -> ANY[]

• The first form allows us to simply pass the list to sort in reverse order based on the system’s default sort settings.
• The second form allows us to specify how NULL values are sorted.

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

Examples

Here’s a list of numbers:

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

Result:

[5, 3, 1]

So instead of sorting in ascending order, it sorted in descending order.

The assumption here is that the default sort order is ascending. DuckDB’s default sort order is in fact ascending, however we can change this to descending if required. More on that later.

For now, here’s another example, this time using a list of strings:

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

Result:

[kiwi, banana, apple]

We can sort date/time values too:

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

Result:

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

We can also sort nested lists:

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

Result:

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

Duplicate Values

Any duplicate values in the list end up next to each other:

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

Result:

[kiwi, kiwi, banana, apple, apple]

Dealing with NULL Values

We can pass a second argument in order to specify how NULL values are sorted:

SELECT 
    list_reverse_sort(['apple', NULL, NULL, 'banana'], 'NULLS_FIRST') AS 'NULLS_FIRST',
    list_reverse_sort(['apple', NULL, NULL, 'banana'], 'NULLS_LAST') AS 'NULLS_LAST';

Result:

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

DuckDB also provides us with the ability to change this with the default_null_order configuration option. We can also set default_order to determine whether the default ordering is ascending or descending.

When DuckDB’s Default Order is Descending

As mentioned, we can set the default_order configuration option so that the default ordering is descending. When we do this, the results of list_reverse_sort() are the reverse of what are when the default ordering is ascending. The default order in DuckDB is ascending, so given list_reverse_sort() reverses this to descending, if we set default_order to descending, then list_reverse_sort() sorts the list in ascending order.

Here’s an example of what I mean:

SET default_order = 'DESC';
SELECT list_reverse_sort( [3, 5, 1] );

Result:

[1, 3, 5]

And let’s reset it to ascending:

SET default_order = 'ASC';
SELECT list_reverse_sort( [3, 5, 1] );

Result:

[5, 3, 1]

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 |
+------------+

That includes checking how NULL values are dealt with.

An Alias: The array_reverse_sort() Function

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

SELECT 
    array_reverse_sort(['apple', NULL, NULL, 'banana'], 'NULLS_FIRST') AS 'NULLS_FIRST',
    array_reverse_sort(['apple', NULL, NULL, 'banana'], 'NULLS_LAST') AS 'NULLS_LAST';

Result:

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