The SQL MIN() Function

SQL has a variety of powerful aggregate tools, including the SQL MIN() function. This article is a quick reference guide to extracting and using minimum values in your queries.

In today’s cutthroat business environment, it’s vital to be able to analyze your organization’s data effectively to make informed decisions. SQL’s aggregate functions let you carry out all kinds of statistical analysis on your data.

This article is a reference guide to the SQL MIN() function, which extracts the smallest item from a column of data.

If you're new to SQL, you may be interested in our interactive SQL Basics course. Starting from the beginner level, you'll become proficient in the most useful features of SQL. You'll learn the concepts of relational databases, how to extract data, and how to analyze it using groupings and aggregates.

You'll connect to a database through your browser to practice your new skills with 129 interactive exercises. The course takes about 10 hours to complete.

Now, let’s get back to the MIN() function. We’ll start by looking at the dataset.

Sample Data

All the examples in this article use the data below, which is a subset of MySQL's sample employee database. The data is held in a table named employee_data.

Simple Examples of the SQL MIN() Function

Example 1: Simple MIN() Function

The MIN() function extracts the minimum value from a column. In its simplest form, you could use the query below to answer the question: How much does the lowest-paid employee earn annually?

SELECT 
  MIN(salary) 
FROM employee_data;

This gives the following results:

Note that the MIN() function, like all other SQL aggregates, is always followed by a column name in brackets. In this example, SQL searches through the entire table and extracts the lowest value for the named column.

Example 2: Using MIN() with Other Aggregate Functions

You can, of course, include other aggregate functions in your results, like this:

SELECT 
  MIN(salary), 
  MAX(salary), 
  AVG(salary) 
FROM employee_data;

Your results would look like this:

For more examples, read SQL MIN() and MAX() Functions Explained in 6 Examples.

Example 3: Using MIN() with an Arithmetic Expression

You can also use arithmetic expressions with the MIN() function. If you wanted to see the minimum monthly salary rather than the minimum annual salary, your query would be:

SELECT MIN(salary/12) AS min_monthly_salary
FROM employee_data;

The result for this query is:

Non-Standard Examples of the MIN() Function

Example 4: Using MIN() on Date/Datetime Columns

The MIN() function works equally well with non-numeric data.

It can be used with date and datetime data types to extract the earliest date or time. If you wanted to find the date of birth of the youngest employee in the company, your query would look like this:

SELECT MIN(birth_date) 
FROM employee_data;

This gives the following results:

Example 5: Using the SQL MIN() Function on Text Columns

For character string data types like CHAR, VARCHAR and TEXT, the SQL MIN() function extracts the first value in alphabetical order from the column.

If you wanted to find the first department name alphabetically in the employee_data table, the query would be:

SELECT MIN(dept_name) 
FROM employee_data

Here are the results:

You’ll notice that even though several employees belong to the Development department, the department name only appears once in the results.

Using the SQL MIN() Function with GROUP BY

The MIN() function, like all the SQL aggregate functions, can be used in conjunction with the GROUP BY clause. This clause tells SQL to group similar rows together and output a single row for each group. For example, if you grouped the sample data by department, your result set would contain one row for each department.

If you’re not yet familiar with the GROUP BY clause and how to use it, these  5 Examples of GROUP BY may help you.

When you use the MIN() function together with the GROUP BY clause, you see the minimum value in each group. For example, if you wanted to see the lowest salary in each department, your query would look like this:

SELECT 
  dept_name, 
  MIN(salary) 
FROM employee_data 
GROUP BY dept_name;

The query results would look like this:

You may want to use the ORDER BY clause as well. If you wanted your results to be in order of minimum salary, your query would be:

SELECT 
  dept_name, 
  MIN(salary) 
FROM employee_data 
GROUP BY dept_name
ORDER BY MIN(salary);

The result set then appears in order of minimum salary, from lowest to highest:

If you feel you need some practice with grouping data in SQL, you’ll find some useful exercises in 10 GROUP BY SQL Practice Exercises with Solutions.

Using the MIN() Function in HAVING

You can use aggregate functions to filter the result set. Normally, selection criteria are defined using a WHERE clause, but you can’t use WHERE to filter by an aggregate. You have to use the HAVING clause instead.

To find all departments where the minimum annual salary is greater than 60k, your query would be:

SELECT dept_name, MIN(salary) 
FROM employee_data 
GROUP BY dept_name
HAVING MIN(salary) > 60000
ORDER BY MIN(salary);

Notice that the HAVING clause must be used with the GROUP BY clause and that it’s placed after the GROUP BY but before the ORDER BY.

The results are:

Do Null Values Affect the MIN() Function?

The SQL MIN() function ignores any rows that have a null value when it calculates the minimum. The value returned is therefore the lowest non-null value in the specified column.

In the sample data, only two rows have values in the termination date. In all the other rows, this column is set to NULL. If you searched for the minimum termination date, you should get the lowest non-null value. Let’s try it out:

SELECT 
  MIN(term_date) 
FROM employee_data;

This returns the smallest date, ignoring the null values:

The only time the MIN() function would return a NULL would be if all the values in the column were NULL.

Moving On with SQL’s MIN() Function

We’ve explored the uses of MIN(), but your learning journey is just getting started; there’s lots more to discover! For a quick how-to guide on using the SQL MIN() function, check out these two cookbooks:

• How to Find the Minimum Value of a Column
• Find Rows with the Minimum Value

There’s nothing like hands-on problem solving to learn any topic really well. Our SQL Practice track sets you a series of challenges similar to those you’ll encounter in the real world, with help always available if you get stuck.

Finally, LearnSQL.com has a huge range of courses that are designed to help you at every stage of your learning journey. If you’re serious about becoming an SQL expert, I’d recommend our SQL From A to Z learning track. This track consists of 7 interactive courses that take you all the way from beginner to advanced functions.

Take the next step and boost your career by becoming an SQL expert!