DuckDB has a min() function just like most RDBMSs that returns the minimum value from a set of values. However, DuckDB’s implementation also allows us to return the bottom n minimum values, which is not something we see in most other RDBMSs.

This article presents some examples of DuckDB’s implementation of the min() function, so as to demonstrate its basic usage, as well as its bottom n functionality.

Syntax

The min() function can be used in the following two ways:

min(arg)
min(arg, n)

Where arg is name of the column from which we want to find the minimum value.

The n argument is optional. If we provide that, it specifies how many results to return (i.e. the bottom n minimum values).

Example

Suppose we create and populate the following table:

-- Create the table
CREATE TABLE sales_data (
    id INTEGER,
    amount DECIMAL(10, 2),
    order_date DATE,
    region TEXT
);

-- Insert sample data
INSERT INTO sales_data (id, amount, order_date, region) VALUES
(1, 200.00, '2023-01-01', 'North'),
(2, 50.00, '2023-02-15', 'South'),
(3, 150.00, '2023-03-10', 'North'),
(4, 300.00, '2023-04-22', 'East'),
(5, 250.00, '2023-05-05', 'South'),
(6, 400.00, '2023-06-18', 'East'),
(7, 350.00, '2023-07-30', 'North'),
(8, 500.00, '2023-08-12', 'West'),
(9, 600.00, '2023-09-25', 'West'),
(10, 450.00, '2023-10-10', 'South');

-- Return all data
SELECT * FROM sales_data;

Output:

+----+--------+------------+--------+
| id | amount | order_date | region |
+----+--------+------------+--------+
| 1  | 200.00 | 2023-01-01 | North  |
| 2  | 50.00  | 2023-02-15 | South  |
| 3  | 150.00 | 2023-03-10 | North  |
| 4  | 300.00 | 2023-04-22 | East   |
| 5  | 250.00 | 2023-05-05 | South  |
| 6  | 400.00 | 2023-06-18 | East   |
| 7  | 350.00 | 2023-07-30 | North  |
| 8  | 500.00 | 2023-08-12 | West   |
| 9  | 600.00 | 2023-09-25 | West   |
| 10 | 450.00 | 2023-10-10 | South  |
+----+--------+------------+--------+

To find the minimum sales amount, we can use the following query:

SELECT 
    min(amount) AS min_sales
FROM sales_data;

Result:

+-----------+
| min_sales |
+-----------+
| 50.00     |
+-----------+

Return the Bottom n Minimum Values

We can provide a second argument to specify how many values to return:

SELECT 
    min(amount, 3) AS bottom_3_sales
FROM sales_data;

Result:

+-------------------------+
|     bottom_3_sales      |
+-------------------------+
| [50.00, 150.00, 200.00] |
+-------------------------+

When we do this, the result is returned as a list containing the specified number of minimum values. In this example I specified that the bottom three minimum values be returned.

Using min() with a WHERE Clause

We can also use the min() function with a WHERE clause to filter the data before finding the minimum value. For example, if we want to find the minimum sales amount for a specific region, we can use:

SELECT 
    min(amount) AS min_sales
FROM sales_data
WHERE region = 'West';

Result:

+-----------+
| min_sales |
+-----------+
| 500.00    |
+-----------+

Finding the Minimum Value in a Date Column

Here’s an example of min() being applied to a date column:

SELECT 
    min(order_date) AS first_order
FROM sales_data;

Result:

+-------------+
| first_order |
+-------------+
| 2023-01-01  |
+-------------+

Here are the bottom two dates:

SELECT 
    min(order_date, 2) AS oldest_orders
FROM sales_data;

Result:

+--------------------------+
|      oldest_orders       |
+--------------------------+
| [2023-01-01, 2023-02-15] |
+--------------------------+

Using min() with GROUP BY

The min() function can be combined with the GROUP BY clause to find the minimum value for each group in a table. For example, if we want to find the minimum sales amount for each region in the sales table, we can use:

SELECT 
    region, 
    min(amount) AS min_sales
FROM sales_data
GROUP BY region;

Result:

+--------+-----------+
| region | min_sales |
+--------+-----------+
| North  | 150.00    |
| South  | 50.00     |
| East   | 300.00    |
| West   | 500.00    |
+--------+-----------+

This query returns the lowest sales amount for each region.

Using min() with Window Functions

DuckDB also supports window functions, which allow us to perform calculations across a set of table rows that are somehow related to the current row.

We can use the min() function as a window function to find the minimum value within a specific window of rows. For example:

SELECT 
    id, 
    amount, 
    region, 
    min(amount) OVER (PARTITION BY region) AS min_sales_in_region
FROM sales_data
ORDER BY region;

Result:

+----+--------+--------+---------------------+
| id | amount | region | min_sales_in_region |
+----+--------+--------+---------------------+
| 4  | 300.00 | East   | 300.00              |
| 6  | 400.00 | East   | 300.00              |
| 1  | 200.00 | North  | 150.00              |
| 3  | 150.00 | North  | 150.00              |
| 7  | 350.00 | North  | 150.00              |
| 2  | 50.00  | South  | 50.00               |
| 5  | 250.00 | South  | 50.00               |
| 10 | 450.00 | South  | 50.00               |
| 8  | 500.00 | West   | 500.00              |
| 9  | 600.00 | West   | 500.00              |
+----+--------+--------+---------------------+

This query returns the minimum sales amount for each region, alongside each individual sale.