{"id":41856,"date":"2025-02-07T04:42:47","date_gmt":"2025-02-06T18:42:47","guid":{"rendered":"https:\/\/database.guide\/?p=41856"},"modified":"2025-02-07T04:42:47","modified_gmt":"2025-02-06T18:42:47","slug":"examples-that-demonstrate-duckdbs-min-function","status":"publish","type":"post","link":"https:\/\/database.guide\/examples-that-demonstrate-duckdbs-min-function\/","title":{"rendered":"Examples that Demonstrate DuckDB’s MIN() Function"},"content":{"rendered":"\n

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

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

Syntax<\/h2>\n\n\n\n

The min()<\/code> function can be used in the following two ways:<\/p>\n\n\n\n

min(arg)\nmin(arg, n)<\/code><\/pre>\n\n\n\n
Where arg<\/code><\/em> is name of the column from which we want to find the minimum value.<\/p>\n\n\n\n
The n<\/code><\/em> argument is optional. If we provide that, it specifies how many results to return (i.e. the bottom n<\/code><\/em> minimum values).<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Suppose we create and populate the following table:<\/p>\n\n\n\n
-- Create the table\nCREATE TABLE sales_data (\n    id INTEGER,\n    amount DECIMAL(10, 2),\n    order_date DATE,\n    region TEXT\n);\n\n-- Insert sample data\nINSERT INTO sales_data (id, amount, order_date, region) VALUES\n(1, 200.00, '2023-01-01', 'North'),\n(2, 50.00, '2023-02-15', 'South'),\n(3, 150.00, '2023-03-10', 'North'),\n(4, 300.00, '2023-04-22', 'East'),\n(5, 250.00, '2023-05-05', 'South'),\n(6, 400.00, '2023-06-18', 'East'),\n(7, 350.00, '2023-07-30', 'North'),\n(8, 500.00, '2023-08-12', 'West'),\n(9, 600.00, '2023-09-25', 'West'),\n(10, 450.00, '2023-10-10', 'South');\n\n-- Return all data\nSELECT * FROM sales_data;<\/code><\/pre>\n\n\n\n
Output:<\/p>\n\n\n\n
+----+--------+------------+--------+
| 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  |
+----+--------+------------+--------+<\/pre>\n\n\n\n
To find the minimum sales amount, we can use the following query:<\/p>\n\n\n\n
SELECT \n    min(amount) AS min_sales\nFROM sales_data;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------+
| min_sales |
+-----------+
| 50.00     |
+-----------+<\/pre>\n\n\n\n
Return the Bottom n<\/code><\/em> Minimum Values<\/h2>\n\n\n\n
We can provide a second argument to specify how many values to return:<\/p>\n\n\n\n
SELECT \n    min(amount, 3) AS bottom_3_sales\nFROM sales_data;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------------------+
|     bottom_3_sales      |
+-------------------------+
| [50.00, 150.00, 200.00] |
+-------------------------+<\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
Using min()<\/code> with a WHERE<\/code> Clause<\/h2>\n\n\n\n
We can also use the min()<\/code> function with a WHERE<\/code> 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:<\/p>\n\n\n\n
SELECT \n    min(amount) AS min_sales\nFROM sales_data\nWHERE region = 'West';<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------+
| min_sales |
+-----------+
| 500.00    |
+-----------+<\/pre>\n\n\n\n
Finding the Minimum Value in a Date Column<\/h2>\n\n\n\n
Here’s an example of min()<\/code> being applied to a date column:<\/p>\n\n\n\n
SELECT \n    min(order_date) AS first_order\nFROM sales_data;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------+
| first_order |
+-------------+
| 2023-01-01  |
+-------------+<\/pre>\n\n\n\n
Here are the bottom two dates:<\/p>\n\n\n\n
SELECT \n    min(order_date, 2) AS oldest_orders\nFROM sales_data;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------+
|      oldest_orders       |
+--------------------------+
| [2023-01-01, 2023-02-15] |
+--------------------------+<\/pre>\n\n\n\n
Using min()<\/code> with GROUP BY<\/code><\/h2>\n\n\n\n
The min()<\/code> function can be combined with the GROUP BY<\/code> clause<\/a> 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<\/code> table, we can use:<\/p>\n\n\n\n
SELECT \n    region, \n    min(amount) AS min_sales\nFROM sales_data\nGROUP BY region;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------+-----------+
| region | min_sales |
+--------+-----------+
| North  | 150.00    |
| South  | 50.00     |
| East   | 300.00    |
| West   | 500.00    |
+--------+-----------+<\/pre>\n\n\n\n
This query returns the lowest sales amount for each region.<\/p>\n\n\n\n
Using min()<\/code> with Window Functions<\/h2>\n\n\n\n
DuckDB also supports window functions<\/a>, which allow us to perform calculations across a set of table rows that are somehow related to the current row. <\/p>\n\n\n\n
We can use the min()<\/code> function as a window function to find the minimum value within a specific window of rows. For example:<\/p>\n\n\n\n
SELECT \n    id, \n    amount, \n    region, \n    min(amount) OVER (PARTITION BY region) AS min_sales_in_region\nFROM sales_data\nORDER BY region;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----+--------+--------+---------------------+
| 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              |
+----+--------+--------+---------------------+<\/pre>\n\n\n\n
This query returns the minimum sales amount for each region, alongside each individual sale.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[174],"tags":[73,93,20],"class_list":["post-41856","post","type-post","status-publish","format-standard","hentry","category-duckdb","tag-aggregate-functions","tag-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/41856","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=41856"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/41856\/revisions"}],"predecessor-version":[{"id":41870,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/41856\/revisions\/41870"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=41856"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=41856"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=41856"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}