SQL MIN

SQL MIN

 

SQL MIN



Summary: This tutorial helps you master the SQL MIN function through practical examples. After the tutorial, you will know how to apply the MIN function effectively to find the minimum value in a set of values.

Introduction to SQL MIN function

The SQL MIN function returns the minimum value in a set of values. The following demonstrates the syntax of the MIN function.

MIN(expression)

Like the MAX function, the MIN function also ignores NULL values and the DISTINCT option is not applicable to the MIN function.

SQL MIN function examples

We will use the employees table to demonstrate the functionality of the MIN function.

To find the lowest (minimum) salary of employees, you apply the MIN function to the salary column of the employees table.

SELECT MIN(salary) FROM employees;

To get the information about the employee who has the lowest salary, you use the following subquery:

SELECT employee_id, first_name, last_name, salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees );

First, the subquery returns the minimum salary. Then, the outer query retrieves the employee whose salary is equal to the lowest salary returned by the subquery.

SQL MIN with GROUP BY example

We often use the MIN function together with the GROUP BY clause to find the minimum value in each group.

For instance, the following query returns the employees who have the lowest salaries in each department.

SELECT department_id, MIN(salary) FROM employees GROUP BY department_id;

The GROUP BY clause groups the employees by department. For each group, the query returns the row with the lowest salary.

The result set is not informative as long as we see the names of the department.

To combine the department’s name in the result set, we need to join the employees table with the departments using the inner join clause as follows:

SELECT d.department_id, department_name, MIN(salary) FROM employees e INNER JOIN departments d ON d.department_id = e.department_id GROUP BY d.department_id;

SQL MIN with ORDER BY example

To sort the groups by the result of the MIN function, you need to put the MIN function in the ORDER BY clause.

The following query first retrieves the employee who has the lowest salary in each department, then sorts these departments by the salary in ascending order.

SELECT d.department_id, department_name, MIN(salary) FROM employees e INNER JOIN departments d ON d.department_id = e.department_id GROUP BY d.department_id ORDER BY MIN(salary);

SQL MIN with HAVING example

You use the HAVING clause to specify the filter condition for groups. To filter the groups based on the result of the MIN function, you place the MIN function in the HAVING clause.

For example, the following query retrieves the employees who have the lowest salary in each department. Then, it includes only departments whose salary is less than 3000.

SELECT d.department_id, department_name, MIN(salary) FROM employees e INNER JOIN departments d ON d.department_id = e.department_id GROUP BY d.department_id HAVING MIN(salary) < 3000;

So now you should know how to use the SQL MIN function to find the minimum value in a set of values.

Reactions

Post a Comment

0 Comments

close