LIST_REVERSE() vs LIST_REVERSE_SORT() in DuckDB: What’s the Difference?

Posted on by Ian

DuckDB has a good range of functions for dealing with lists and arrays. Amongst these are list_reverse() and a list_reverse_sort(). Looking at their names, you could be forgiven for thinking that these do the same thing. But they don’t.

If you’re wondering why DuckDB has a list_reverse() and a list_reverse_sort() function, read on.

The Difference

Here’s what each function does:

So one function sorts the list (in reverse order) and the other one simply reverses the list (with no sorting).

Example

Here’s a simple example that demonstrates the difference between these two functions:

SELECT 
    list_reverse_sort([ 3, 1, 7 ]) AS list_reverse_sort,
    list_reverse([ 3, 1, 7 ]) AS list_reverse;

Result:

+-------------------+--------------+
| list_reverse_sort | list_reverse |
+-------------------+--------------+
| [7, 3, 1]         | [7, 1, 3]    |
+-------------------+--------------+

We can see that list_reverse_sort() sorted the list in reverse order, whereas list_reverse() simply reversed the items in the list.

The Default Sort Order Matters

DuckDB’s default sort order is ascending. But this can be changed to descending by setting the default_order configuration option to DESC. If we do this, the results of list_reverse_sort() will be different. “Reverse” order will actually be ascending instead of descending like in the above example.

However, the results of list_reverse() will remain unchanged (because no sorting occurs with this function).

Here’s an example of what I mean:

SET default_order = 'DESC';
SELECT 
    list_reverse_sort([ 3, 1, 7 ]) AS list_reverse_sort,
    list_reverse([ 3, 1, 7 ]) AS list_reverse;

Result:

+-------------------+--------------+
| list_reverse_sort | list_reverse |
+-------------------+--------------+
| [1, 3, 7]         | [7, 1, 3]    |
+-------------------+--------------+

We can confirm that list_reverse_sort() now sorts its results in ascending order, due to that being the reverse of descending.

We can set the default sort order back to ascending like this:

SET default_order = 'ASC';

Comparing with list_sort()

There’s also a list_sort() function that sorts lists in the default sort order.

Here’s another example. This time we use list_reverse(), list_sort() and list_reverse_sort() against the same list, so that we can compare the difference between all functions:

.mode line
SET VARIABLE fruit = ['apple', 'kiwi', 'grape', 'banana'];

SELECT 
    getvariable('fruit') AS original_list,
    list_reverse(getvariable('fruit')) AS list_reverse,
    list_sort(getvariable('fruit')) AS list_sort,
    list_reverse_sort(getvariable('fruit')) AS list_reverse_sort;

Result:

    original_list = [apple, kiwi, grape, banana]
     list_reverse = [banana, grape, kiwi, apple]
        list_sort = [apple, banana, grape, kiwi]
list_reverse_sort = [kiwi, grape, banana, apple]

In this example I used .mode line in order to enable vertical output. Also, just to be sure, the default sort order is ASC.