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.
lists
4 Ways to Concatenate 3 or More Lists in DuckDB
If you’ve ever used list_concat() or any of its aliases to concatenate lists in DuckDB, you may have been disappointed to find out that it only concatenates two lists. Any more than two lists and it returns an error. At least that’s how it works at the time of this writing.
Fortunately, there are some alternatives we can use in order to concatenate three or more lists.
Here are four ways to concatenate three or more lists in DuckDB.
A Quick Look at DuckDB’s LIST_WHERE() Function
DuckDB has a list_where() function that returns a list after a user-supplied mask has been applied. We pass the list as the first argument, and a list of Boolean values as the second. The list of Boolean values is applied as a mask to the list, which determines which values in the list are returned.
Outputting Query Results as a TCL List in the DuckDB CLI
This article demonstrates how to output your DuckDB query results in TCL list format, which can be useful when integrating with TCL scripts or systems that expect TCL-formatted data.
Using Bracket Notation to Slice a List in DuckDB
DuckDB provides a few options for slicing lists, including the list_slice() function and its alias, array_slice(). We can also use bracket notation for a slightly more concise option. Bracket notation is where we append the slice details to the list.
Below are examples of using bracket notation to slice lists in DuckDB.
3 Ways to Get the Average From a List of Numbers in DuckDB
When presented with a list of numbers, there are many things we might want to do with it. One is to get the average value of all values in the list. Fortunately DuckDB provides us with several ways to do this.
Using LIST_RESIZE() to Resize a List in DuckDB
DuckDB provides us with a list_resize() function for those occasions where we need to resize a list. It also offers an alias called array_resize() that does exactly the same thing. When we resize a list using these functions, we specify how big we want the resulting list to be, and we can also specify a value to use for any extra elements that are added to the list.
A Quick Look at the REPEAT() Function in DuckDB
DuckDB has a repeat() function that enables us to output a repeating value easily and concisely. The way it works is that we pass the value to the function, followed by how many times we want it to be repeated. It returns that value repeated the specified number of times.
LIST_REVERSE() vs LIST_REVERSE_SORT() in DuckDB: What’s the Difference?
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.
3 Ways to Slice a List in DuckDB
DuckDB provides us with a few options for slicing lists. Slicing a list is where we take a sublist or “slice” from the list, rather than the whole list. We specify the start and end position for which to return the slice. We can also specify a step, which allows us to skip one or more elements along the slice.
