Most SQL developers are familiar with the min() function that allows us to get the minimum value from a data set. But if we want to get the minimum value from a list, passing the list to the min() function won’t quite cut it. But don’t despair! Finding the minimum value in a list is just as easy. Here are three ways to do it. And yes, we can even use the min() function if we want.
1. The list_min() Function
DuckDB has a list_min() function, which is basically a min() function for lists. We simply pass the list to the function, and it returns the minimum value from that list:
SELECT list_min([ 10, 7, 3, 34 ]);Result:
3
As expected, the function returned the minimum value from the list.
The list can contain other values, such as strings, dates, and even other lists:
SELECT
list_min([ 'Duck', 'Zebra', 'Elephant' ]) AS strings,
list_min([ date '2025-01-01', date '2000-01-01', date '1999-01-01' ]) AS dates,
list_min([ [1,2,3], [4,5], [1,2,3,4] ]) AS nested_lists;Result:
+---------+------------+--------------+
| strings | dates | nested_lists |
+---------+------------+--------------+
| Duck | 1999-01-01 | [1, 2, 3] |
+---------+------------+--------------+
2. The list_aggregate() Function
DuckDB’s list_aggregate() function enables us to run an aggregate function without calling that aggregate function directly. We pass the name of the aggregate function to list_aggregate(), which then behaves as though it’s that aggregate function:
SELECT
list_aggregate([ 10, 7, 3, 34 ], 'min') AS numbers,
list_aggregate([ 'Duck', 'Zebra', 'Elephant' ], 'min') AS strings,
list_aggregate([ date '2025-01-01', date '2000-01-01', date '1999-01-01' ], 'min') AS dates,
list_aggregate([ [1,2,3], [4,5], [1,2,3,4] ], 'min') AS nested_lists;Result:
+---------+---------+------------+--------------+
| numbers | strings | dates | nested_lists |
+---------+---------+------------+--------------+
| 3 | Duck | 1999-01-01 | [1, 2, 3] |
+---------+---------+------------+--------------+
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 min() Function
Although the min() function is designed to work on tabular data and not lists, if we unnest the list, then we can use it on the unnested list. That’s because when we unnest a list it is returned in tabular form. Here’s an example of using min() against an unnested list:
SELECT min(l)
FROM unnest([ 10, 7, 3, 34 ]) AS t(l);Result:
3
The min() function also accepts a second argument, which allows us to specify how many values to return. For example, if we want the top two minimum values, we can pass 2 as the second argument:
SELECT min(l, 2)
FROM unnest([ 10, 7, 3, 34 ]) AS t(l);Result:
[3, 7]
In this case the result is returned as a list.
Here’s the bottom three values from a longer list:
SELECT min(l, 3)
FROM unnest([ 10, 7, 25, 3, 56, 78, 1 ]) AS t(l);Result:
[1, 3, 7]
Oh and in case you were wondering, here’s what happens when we pass the list to min() without unnesting it:
SELECT min([ 10, 7, 3, 34 ]);Result:
[10, 7, 3, 34]
It simply returns the same list. That’s because as far as min() is concerned, we only passed one value (the whole list), and so it returned the minimum value, which is the list itself.