DuckDB has a min_by() function that’s similar to the min() function, but with a twist. While the min() function simply returns the minimum value, the min_by() function finds the row with the minimum value in one column and returns the corresponding value from another column at that row.

Here are some examples that demonstrate how it works.

Syntax

First, it’s important to know that the min_by() function has two forms:

1. Two-argument form:

min_by(arg, val)

2. Three-argument form:

min_by(arg, val, n)

Where:

• arg: The value to return from the row with minimum val
• val: The value to find the minimum of
• n: (Optional) Number of values to return per group

Basically, the second syntax allows us to specify more than one row, so that we can get the bottom n minimum values instead of just the bottom one.

Example 1: Basic Usage

Let’s create and populate a table for our example:

-- Create a sales table
CREATE TABLE sales (
    sale_date DATE,
    product_name VARCHAR,
    quantity INT,
    revenue DECIMAL(10,2)
);

-- Insert sample data
INSERT INTO sales VALUES
    ('2024-01-01', 'Widget A', 10, 150.00),
    ('2024-01-02', 'Widget B', 5, 200.00),
    ('2024-01-03', 'Widget A', 15, 225.00),
    ('2024-01-04', 'Widget B', 8, 180.00),
    ('2024-01-05', 'Widget C', 12, 180.00);

-- Select all data from the table
SELECT * FROM sales;

Output:

+------------+--------------+----------+---------+
| sale_date  | product_name | quantity | revenue |
+------------+--------------+----------+---------+
| 2024-01-01 | Widget A     | 10       | 150.00  |
| 2024-01-02 | Widget B     | 5        | 200.00  |
| 2024-01-03 | Widget A     | 15       | 225.00  |
| 2024-01-04 | Widget B     | 8        | 180.00  |
| 2024-01-05 | Widget C     | 12       | 180.00  |
+------------+--------------+----------+---------+

Now let’s use the min_by() function to find the product name from the row with the lowest single-sale revenue:

SELECT 
    min_by(product_name, revenue) as lowest_revenue_product
FROM sales;

Result:

+------------------------+
| lowest_revenue_product |
+------------------------+
| Widget A               |
+------------------------+

Example 2: Using min_by() with GROUP BY

This example uses min_by() with the GROUP BY clause.

First, let’s create and populate another table:

-- Create an employees table
CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR,
    department VARCHAR,
    salary DECIMAL(10,2),
    hire_date DATE
);

-- Insert sample data
INSERT INTO employees VALUES
    (1, 'Beavis Blakefield', 'Sales', 75000.00, '2022-01-15'),
    (2, 'Mahavishnu Mars', 'Sales', 82000.00, '2021-06-20'),
    (3, 'Veralda Vett', 'Engineering', 95000.00, '2020-03-10'),
    (4, 'Hank Heckler', 'Engineering', 70000.00, '2021-07-11'),
    (5, 'Slash Slater', 'Engineering', 98000.00, '2019-11-25'),
    (6, 'Amy Amish', 'Engineering', 87000.00, '2019-12-14'),
    (7, 'Bon Butler', 'Marketing', 70000.00, '2023-02-01'),
    (8, 'Dave Danker', 'Marketing', 72000.00, '2022-09-15'),
    (9, 'Sally Schmit', 'Marketing', 82000.00, '2017-10-07');

-- Select all data from the table
SELECT * FROM employees;

Output:

+--------+-------------------+-------------+----------+------------+
| emp_id |     emp_name      | department  |  salary  | hire_date  |
+--------+-------------------+-------------+----------+------------+
| 1      | Beavis Blakefield | Sales       | 75000.00 | 2022-01-15 |
| 2      | Mahavishnu Mars   | Sales       | 82000.00 | 2021-06-20 |
| 3      | Veralda Vett      | Engineering | 95000.00 | 2020-03-10 |
| 4      | Hank Heckler      | Engineering | 70000.00 | 2021-07-11 |
| 5      | Slash Slater      | Engineering | 98000.00 | 2019-11-25 |
| 6      | Amy Amish         | Engineering | 87000.00 | 2019-12-14 |
| 7      | Bon Butler        | Marketing   | 70000.00 | 2023-02-01 |
| 8      | Dave Danker       | Marketing   | 72000.00 | 2022-09-15 |
| 9      | Sally Schmit      | Marketing   | 82000.00 | 2017-10-07 |
+--------+-------------------+-------------+----------+------------+

Now we’ll find the name of the lowest-paid employee in each department, along with their salary:

SELECT 
    department,
    min_by(emp_name, salary) as lowest_paid_employee,
    min(salary) as min_salary
FROM employees
GROUP BY department
ORDER BY department;

Result:

+-------------+----------------------+------------+
| department  | lowest_paid_employee | min_salary |
+-------------+----------------------+------------+
| Engineering | Hank Heckler         | 70000.00   |
| Marketing   | Bon Butler           | 70000.00   |
| Sales       | Beavis Blakefield    | 75000.00   |
+-------------+----------------------+------------+

Example 3: Multiple Values per Group

As mentioned, we can provide a third argument in order to return the top n rows. Here’s an example:

SELECT 
    department,
    min_by(emp_name, salary, 2) as bottom_employees
FROM employees
GROUP BY department;

Result:

+-------------+--------------------------------------+
| department  |           bottom_employees           |
+-------------+--------------------------------------+
| Engineering | [Hank Heckler, Amy Amish]            |
| Marketing   | [Bon Butler, Dave Danker]            |
| Sales       | [Beavis Blakefield, Mahavishnu Mars] |
+-------------+--------------------------------------+

This query returned the names of the two lowest-paid employees in each department. The results are returned as a LIST when we do this.

Using min_by() with Dates

We can use min_by() with dates to get the value with the most recent date:

SELECT 
    department,
    min_by(emp_name, hire_date) as first_hire
FROM employees
GROUP BY department
ORDER BY department;

Result:

+-------------+-----------------+
| department  |   first_hire    |
+-------------+-----------------+
| Engineering | Slash Slater    |
| Marketing   | Sally Schmit    |
| Sales       | Mahavishnu Mars |
+-------------+-----------------+

This query returned the employee with the oldest hire date in each department.

Synonyms/Aliases

The function also has some aliases, which results in three equivalent names:

• min_by()
• arg_min()
• argMin()

All forms are identical in functionality and can be used interchangeably. Therefore, we can replace any of the above examples with these aliases:

SELECT 
    department,
    min_by(emp_name, hire_date) as min_by,
    arg_min(emp_name, hire_date) as arg_min,
    argMin(emp_name, hire_date) as argMin
FROM employees
GROUP BY department;

Result:

+-------------+-----------------+-----------------+-----------------+
| department  |     min_by      |     arg_min     |     argMin      |
+-------------+-----------------+-----------------+-----------------+
| Engineering | Slash Slater    | Slash Slater    | Slash Slater    |
| Marketing   | Sally Schmit    | Sally Schmit    | Sally Schmit    |
| Sales       | Mahavishnu Mars | Mahavishnu Mars | Mahavishnu Mars |
+-------------+-----------------+-----------------+-----------------+