Deep amongst DuckDB’s vast selection of list functions lies the list_reverse() function. This function reverses the elements in a list. We simply pass the list to the function and it reverses the order of the elements in that list.

Not to be confused with the list_sort() or list_reverse_sort() functions, the list_reverse() function reverses the order of the items as they currently sit in the list. The ...sort() functions on the other hand, actually perform a sort operation on the list items.

Example

Here’s a simple example to demonstrate:

SELECT list_reverse( [ 'Cloud', 'Ant', 'Box' ] );

Result:

[Box, Ant, Cloud]

As mentioned, it doesn’t sort the list in any way. It simply reverses the order of the items in the list.

Here’s one with numbers:

SELECT list_reverse( [ 3, 27, 1, 235, 12 ] );

Result:

[12, 235, 1, 27, 3]

And dates:

SELECT list_reverse([ 
    date '2041-01-01',
    date '2040-01-01',
    date '2070-01-01',
    date '2050-01-01',
]);

Result:

[2050-01-01, 2070-01-01, 2040-01-01, 2041-01-01]

Nested Lists

We can apply list_reverse() to nested lists:

SELECT list_reverse_sort([
    ['Cloud', 'Ant', 'Box'],
    ['Sky', 'Burr', 'Smash'],
    ['Ticker', 'Light', 'Dark']
    ]);

Result:

[[Ticker, Light, Dark], [Sky, Burr, Smash], [Cloud, Ant, Box]]

Here, the order of each list is reversed within the outer list.

UnnestedLists

We can also use the unnest() function to unnest the nested lists so as to reverse the order of the elements in each list:

SELECT list_reverse( 
    unnest([ 
        ['Cloud', 'Ant', 'Box'],
        ['Sky', 'Burr', 'Smash'],
        ['Ticker', 'Light', 'Dark']
        ])
    ) AS unnested;

Result:

+-----------------------+
|       unnested        |
+-----------------------+
| [Box, Ant, Cloud]     |
| [Smash, Burr, Sky]    |
| [Dark, Light, Ticker] |
+-----------------------+

In this case, each nested list appears in its own row, with its element order reversed.

Here’s what happens if we reverse unnest() and list_reverse(), so that we unnest the result of list_reverse():

SELECT unnest( 
    list_reverse([ 
        ['Cloud', 'Ant', 'Box'],
        ['Sky', 'Burr', 'Smash'],
        ['Ticker', 'Light', 'Dark']
        ])
    ) AS unnested;

Result:

+-----------------------+
|       unnested        |
+-----------------------+
| [Ticker, Light, Dark] |
| [Sky, Burr, Smash]    |
| [Cloud, Ant, Box]     |
+-----------------------+

Flattened Lists

We can also use the flatten() function to flatten the nested lists:

SELECT list_reverse( 
    flatten([ 
        ['Cloud', 'Ant', 'Box'],
        ['Sky', 'Burr', 'Smash'],
        ['Ticker', 'Light', 'Dark']
        ])
    );

Result:

[Dark, Light, Ticker, Smash, Burr, Sky, Box, Ant, Cloud]

In this case, the whole list structure has been flattened, so that all list items are concatenated into a single list. The list_reverse() function has been applied to the result.

Here’s what happens if we reverse the flatten() and list_reverse() functions:

SELECT flatten( 
    list_reverse([ 
        ['Cloud', 'Ant', 'Box'],
        ['Sky', 'Burr', 'Smash'],
        ['Ticker', 'Light', 'Dark']
        ])
    ) AS flattened;

Result:

[Ticker, Light, Dark, Sky, Burr, Smash, Cloud, Ant, Box]

Compared with list_sort() and list_reverse_sort()

Here’s a query that applies list_reverse(), list_sort() and list_reverse_sort() against the same list, so that we can compare the difference between the 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.

An Alias: The array_reverse() Function

DuckDB also has an array_reverse() function, which is an alias for list_array(). Therefore, we can use either one interchangeably. For example:

SELECT 
    array_reverse( [ 'Cloud', 'Ant', 'Box' ] ) AS array_reverse,
    list_reverse( [ 'Cloud', 'Ant', 'Box' ] ) AS list_reverse;

Result:

+-------------------+-------------------+
|   array_reverse   |   list_reverse    |
+-------------------+-------------------+
| [Box, Ant, Cloud] | [Box, Ant, Cloud] |
+-------------------+-------------------+