4 Ways to Get the Last Value from a List in DuckDB

by

When it comes to working with lists, DuckDB often provides us with multiple methods of performing a given task. Getting the last value from a list is no exception.

Here are four options we can use to extract the last value from a list in DuckDB.

1. The list_last() Function

The list_last() function is an obvious option, due to the fact that its sole purpose is to return the last value in a list. All we need to do is pass the list to the function:

SELECT list_last(['Cat', 'Dog', 'Bird']);

Result:

Bird

If the list is full of nested lists, then it returns the applicable list:

SELECT list_last([['Cat', 'Dog'], ['Bird', 'Frog']]);

Result:

[Bird, Frog]

But we can use the unnest() function to unnest the list. When we do this, the result depends on where we put the unnest() function. For example, we can do this:

SELECT list_last([
    unnest(['Cat', 'Dog']), 
    unnest(['Bird', 'Frog'])
]) AS unnested;

Output:

+----------+
| unnested |
+----------+
| Bird     |
| Frog     |
+----------+

Or this:

SELECT list_last(
    unnest([
        ['Cat', 'Dog'], 
        ['Bird', 'Frog']
        ])
) AS unnested;

Output:

+----------+
| unnested |
+----------+
| Dog      |
| Frog     |
+----------+

Or even this:

SELECT unnest(
    list_last([
            ['Cat', 'Dog'], 
            ['Bird', 'Frog']
        ])
    ) AS unnested;

Output:

+----------+
| unnested |
+----------+
| Bird     |
| Frog     |
+----------+

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

SELECT list_last(
    flatten([['Cat', 'Dog'], ['Bird', 'Frog']])
    ) AS flattened;

Output:

+-----------+
| flattened |
+-----------+
| Frog      |
+-----------+

2. The list_aggregate() Function

The list_aggregate() function is a more general function that allows us to specify which aggregate function to apply. We can use pass last to this function to get the same result that list_last() returns:

SELECT list_aggregate(['Cat', 'Dog', 'Bird'], 'last');

Result:

Bird

Any nested lists can have unnest() or flatten() applied wherever applicable.

3. The last() Function

The last() function is an aggregate function that gets the last value of a column. It’s not normally used on lists, but it is possible to use it on a list that’s been unnested:

SELECT last(l)
FROM unnest(['Cat', 'Dog', 'Bird']) AS t(l);

Result:

Bird

To understand this example, here it is without the last() function:

SELECT l
FROM unnest(['Cat', 'Dog', 'Bird']) AS t(l);

Result:

+------+
|  l   |
+------+
| Cat  |
| Dog  |
| Bird |
+------+

The last row contains Bird, and that’s what the last() function returned. It simply chose whatever value was in the last row.

4. Combining LIMIT and list_reverse()

The fourth option is to run a query that returns a result set, but to use LIMIT 1 to return just the first row. But when we do this, we’ll need to reverse the order of the list so that it returns the last item rather than the first item. Fortunately DuckDB has a list_reverse() function that allows us to reverse the list order.

So it will look something like this:

SELECT l
FROM unnest(
    list_reverse(['Cat', 'Dog', 'Bird'])
    ) AS t(l)
LIMIT 1;

Output:

+------+
|  l   |
+------+
| Bird |
+------+