Sometimes when working with DuckDB, we need to find the maximum value from a given list. This can often be the case when working with lists that contain numeric data. Fortunately DuckDB provides a few options for us for doing this.

1. The list_max() Function

DuckDB provides a function that conveniently does exactly what we want. Called list_max(), it returns the maximum value from a list. To use this function, we simply pass the list to the function:

SELECT list_max([ 10, 7, 25, 3 ]);

Result:

25

As expected, the function returned the maximum value from the list.

The list can contain other values, such as strings, dates, and even other lists:

SELECT 
    list_max([ 'Duck', 'Zebra', 'Elephant' ]) AS strings,
    list_max([ date '2025-01-01', date '2000-01-01', date '1999-01-01' ]) AS dates,
    list_max([ [1,2,3], [4,5,6], [1,2,3,4] ]) AS nested_lists;

Result:

+---------+------------+--------------+
| strings |   dates    | nested_lists |
+---------+------------+--------------+
| Zebra   | 2025-01-01 | [4, 5, 6]    |
+---------+------------+--------------+

2. The list_aggregate() Function

DuckDB’s list_aggregate() function enables us to run another function without calling it like we’d normally call it. What I mean is that we pass the name of the aggregate function to list_aggregate(), and then list_aggregate() acts like that aggregate function:

SELECT 
    list_aggregate([ 10, 7, 25, 3 ], 'max') AS numbers,
    list_aggregate([ 'Duck', 'Zebra', 'Elephant' ], 'max') AS strings,
    list_aggregate([ date '2025-01-01', date '2000-01-01', date '1999-01-01' ], 'max') AS dates,
    list_aggregate([ [1,2,3], [4,5,6], [1,2,3,4] ], 'max') AS nested_lists;

Result:

+---------+---------+------------+--------------+
| numbers | strings |   dates    | nested_lists |
+---------+---------+------------+--------------+
| 25      | Zebra   | 2025-01-01 | [4, 5, 6]    |
+---------+---------+------------+--------------+

The list_aggregate() function has a bunch of aliases:

• list_aggr()
• aggregate()
• array_aggregate()
• array_aggr()

All of these work in exactly the same way, so you can choose which one you want to use.

3. The max() Function

Another way to do it is to use the max() function. While this function is not designed to be used on lists, it’s still possible to use it on list data if we unnest the list:

SELECT max(l)
FROM unnest([ 10, 7, 25, 3 ]) AS t(l);

Result:

25

The max() function also accepts a second argument, which allows us to specify how many values to return. For example, if we want the top two maximum values, then we can pass 2 as the second argument:

SELECT max(l, 2)
FROM unnest([ 10, 7, 25, 3 ]) AS t(l);

Result:

[25, 10]

Here it is with a longer list and more top n results:

SELECT max(l, 3)
FROM unnest([ 10, 7, 25, 3, 56, 78, 1 ]) AS t(l);

Result:

[78, 56, 25]