In SQL, the MIN() function is an aggregate function that returns the minimum value in a given expression.
Below are some basic examples to demonstrate how it works.
Sample Table
Suppose we have the following table:
SELECT * FROM Products;Result:
+-------------+------------+---------------------------------+----------------+-----------------------------------------+ | ProductId | VendorId | ProductName | ProductPrice | ProductDescription | |-------------+------------+---------------------------------+----------------+-----------------------------------------| | 1 | 1001 | Left handed screwdriver | 25.99 | Purple. Includes left handed carry box. | | 2 | 1001 | Long Weight (blue) | 14.75 | Includes a long wait. | | 3 | 1001 | Long Weight (green) | 11.99 | Approximate 30 minute waiting period. | | 4 | 1002 | Sledge Hammer | 33.49 | Wooden handle. Free wine glasses. | | 5 | 1003 | Chainsaw | 245.00 | Orange. Includes spare fingers. | | 6 | 1003 | Straw Dog Box | NULL | Tied with vines. Very chewable. | | 7 | 1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | Brown ceramic with solid handle. | +-------------+------------+---------------------------------+----------------+-----------------------------------------+
Example
Here’s a simple example to demonstrate.
SELECT MIN(ProductPrice)
FROM Products;Result:
+--------------------+ | (No column name) | |--------------------| | 9.99 | +--------------------+
In this case, price information is stored in the ProductPrice column, and so we pass that as an argument to the MIN() function, which then calculates and returns the result.
Using Column Aliases
You’ll notice that the previous results don’t include a column name. This is to be expected, because the MIN() function doesn’t return any columns. You can easily provide a column name by assigning an alias.
SELECT MIN(ProductPrice) AS MinimumPrice
FROM Products;Result:
+----------------+ | MinimumPrice | |----------------| | 9.99 | +----------------+
Filtered Results
The MIN() function operates on the rows returned by the query. So if you filter the results, the result of MIN() will reflect that.
SELECT MIN(ProductPrice) AS MinimumPrice
FROM Products
WHERE VendorId = 1001;Result:
+----------------+ | MinimumPrice | |----------------| | 11.99 | +----------------+
In this case, 11.99 is the minimum price out of all the products offered by the specified vendor.
NULL Values
The MIN() function ignores any NULL values. In our sample table above, product number 6 has got NULL in its ProductPrice column, but that was ignored in our MIN() example.
Depending on your DBMS and your settings, you may or may not see a warning that NULL values were eliminated in the result set.
Here’s an example of what you might see:
SELECT MIN(ProductPrice) AS MinimumPrice
FROM Products;Result:
+----------------+ | MinimumPrice | |----------------| | 9.99 | +----------------+ Warning: Null value is eliminated by an aggregate or other SET operation.
All this tells us is that the column contained at least one NULL value, and that it was ignored when calculating the results.
Date/Time Data
You can use MIN() on date/time values.
Suppose we have the following table:
SELECT PetName, DOB
FROM Pets;Result:
+-----------+------------+ | PetName | DOB | |-----------+------------| | Fluffy | 2020-11-20 | | Fetch | 2019-08-16 | | Scratch | 2018-10-01 | | Wag | 2020-03-15 | | Tweet | 2020-11-28 | | Fluffy | 2020-09-17 | | Bark | NULL | | Meow | NULL | +-----------+------------+
We can use MIN() to find the minimum date of birth (DOB).
SELECT MIN(DOB) AS MinDOB
FROM Pets;Result:
+------------+ | MinDOB | |------------| | 2018-10-01 | +------------+
Again, it ignores NULL values.
Character Data
When used with character data columns, MIN() finds the value that is lowest in the sort sequence.
Example:
SELECT MIN(ProductName) AS MinimumProductName
FROM Products;Result:
+---------------------------------+ | MinimumProductName | |---------------------------------| | Bottomless Coffee Mugs (4 Pack) | +---------------------------------+
The DISTINCT Keyword
Your DBMS may allow the DISTINCT keyword to be used with the MIN() function. If so, this is probably in order to comply with the ISO SQL standard.
Therefore, you may be able to do this:
SELECT MIN(DISTINCT ProductPrice) AS MinimumPrice
FROM Products;Result:
+----------------+ | MinimumPrice | |----------------| | 9.99 | +----------------+
But it will have no impact on the results.
The DISTINCT keyword can be very useful when used with other contexts (e.g. with the COUNT() function), but it has no meaning when used with MIN(). The DISTINCT keyword removes duplicates, but in the case of MIN() it won’t make any difference because MIN() returns the same result regardless of how many rows share the same minimum value.
Window Functions
Depending on your DBMS, you may be able to use an OVER clause with your MIN() function to create a window function.
A window function performs an aggregate-like operation on a set of query rows. It produces a result for each query row. This is in contrast to an aggregate operation, which groups query rows into a single result row.
Here’s an example to demonstrate the concept.
We’ve already seen the Products table. Our database also has a Customers table, and it contains the following data:
+--------------+----------------------+-------------------+------------+-----------------+------------+-----------+----------------+ | CustomerId | CustomerName | PostalAddress | City | StateProvince | ZipCode | Country | Phone | |--------------+----------------------+-------------------+------------+-----------------+------------+-----------+----------------+ | 1001 | Palm Pantry | 20 Esplanade | Townsville | QLD | 2040 | AUS | (308) 555-0100 | | 1002 | Tall Poppy | 12 Main Road | Columbus | OH | 43333 | USA | (310) 657-0134 | | 1003 | Crazy Critters | 10 Infinite Loops | Cairns | QLD | 4870 | AUS | (418) 555-0143 | | 1004 | Oops Media | 4 Beachside Drive | Perth | WA | 1234 | AUS | (405) 443-5987 | | 1005 | Strange Names Inc. | 789 George Street | Sydney | NSW | 2000 | AUD | (318) 777-0177 | | 1006 | Hi-Five Solutionists | 5 High Street | Highlands | HI | 1254 | AUS | (415) 413-5182 | +--------------+----------------------+-------------------+------------+-----------------+------------+-----------+----------------+
We can retrieve data from these tables and present them as one result set by using a join.
We can also use the MIN() function with the OVER clause to apply a window function to the data.
SELECT
v.VendorName,
p.ProductName,
p.ProductPrice,
MIN(ProductPrice) OVER (PARTITION BY v.VendorName) AS "Minimum Price For This Vendor"
FROM Products p
INNER JOIN Vendors v
ON v.VendorId = p.VendorId
ORDER BY VendorName, ProductPrice, "Minimum Price For This Vendor";Result:
+---------------+---------------------------------+----------------+---------------------------------+ | VendorName | ProductName | ProductPrice | Minimum Price For This Vendor | |---------------+---------------------------------+----------------+---------------------------------| | Katty Kittens | Bottomless Coffee Mugs (4 Pack) | 9.99 | 9.99 | | Mars Supplies | Long Weight (green) | 11.99 | 11.99 | | Mars Supplies | Long Weight (blue) | 14.75 | 11.99 | | Mars Supplies | Left handed screwdriver | 25.99 | 11.99 | | Pedal Medals | Straw Dog Box | NULL | 245.00 | | Pedal Medals | Chainsaw | 245.00 | 245.00 | | Randy Roofers | Sledge Hammer | 33.49 | 33.49 | +---------------+---------------------------------+----------------+---------------------------------+
In this case we used the OVER clause with our MIN() function to partition the result by vendor name.
By doing this, we were able to return price information for each product, as well as the minimum price for all products from that given vendor. This minimum price changes as the vendor changes (unless multiple vendors happen to have the same minimum price), but remains the same for all products from the same vendor.
This concept can also be applied to other aggregate functions in SQL, such as SUM(), AVG(), MAX(), and COUNT().