Как найти минимум в sql

In this tutorial, you will learn about the MAX() and MIN() functions in SQL with the help of examples.

SELECT MAX(columnn)
FROM table;

SELECT MAX(age)
FROM Customers;

SELECT MIN(columnn)
FROM table;

SELECT MIN(age)
FROM Customers;

-- use max_age as an alias for the maximum age
SELECT MAX(age) AS max_age
FROM Customers;

--select the minimum value of first_name from Customers

SELECT MIN(first_name) AS min_first_name
FROM Customers;

-- MIN() function in a nested SELECT statement

SELECT *
FROM Customers
WHERE age = (
    SELECT MIN(age)
    FROM Customers
);

-- returns 50 as highest
SELECT MAX(20, 30, 50) as highest;

-- returns 20 as lowest
SELECT MIN(20, 30, 50) as lowest;

SELECT *
FROM Customers
GROUP BY country
HAVING MAX(age);

Here are three examples of using SQL to find and select the row with the minimum value in a given column.

The examples work in most major RDBMSs, including MySQL, MariaDB, PostgreSQL, SQLite, Oracle, and SQL Server.

Sample Data

Let’s start with the following data:

SELECT * FROM PetShow;

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 1       | Wag       | 85      |
| 2       | Scratch   | 3       |
| 3       | Tweet     | 65      |
| 4       | Bark      | 8       |
| 5       | Ruff      | 15      |
| 6       | Woof      | 20      |
+---------+-----------+---------+

Option 1

Here’s our first option for selecting the row with the minimum value from the above table:

SELECT 
    PetId,
    PetName,
    Score
FROM PetShow 
WHERE Score = ( SELECT MIN(Score) FROM PetShow );

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 2       | Scratch   | 3       |
+---------+-----------+---------+

Here, we used the MIN() function within a subquery to find the minimum value, and returned the whole row with the outer query.

When there are Multiple Rows with the Min Value

Using this method, if there are multiple rows with the minimum value, all of them are returned.

Suppose we insert another row into our table with the same score as the existing minimum score:

INSERT INTO PetShow VALUES (7, 'Punch', 3);
SELECT * FROM PetShow;

Our table now looks like this:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 1       | Wag       | 85      |
| 2       | Scratch   | 3       |
| 3       | Tweet     | 65      |
| 4       | Bark      | 8       |
| 5       | Ruff      | 15      |
| 6       | Woof      | 20      |
| 7       | Punch     | 3       |
+---------+-----------+---------+

We can see that both Scratch and Punch have got the low score of 3.

Let’s run the previous query again to return the minimum value from that column:

SELECT 
    PetId,
    PetName,
    Score
FROM PetShow 
WHERE Score = ( SELECT MIN(Score) FROM PetShow );

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 2       | Scratch   | 3       |
| 7       | Punch     | 3       |
+---------+-----------+---------+

Both rows with the minimum values are returned.

We can limit the result set to just one row if required. The exact code will depend on the RDBMS being used.

The LIMIT clause can be used with RDBSs such as PostgreSQL, MariaDB, MySQL, and SQLite:

SELECT 
    PetId,
    PetName,
    Score
FROM PetShow 
WHERE Score = ( SELECT MIN(Score) FROM PetShow )
ORDER BY PetId ASC
LIMIT 1;

Result:

+-------+---------+-------+
| PetId | PetName | Score |
+-------+---------+-------+
|     2 | Scratch |     3 |
+-------+---------+-------+

In SQL Server, we can use the TOP clause:

SELECT TOP 1
    PetId,
    PetName,
    Score
FROM PetShow 
WHERE Score = ( SELECT MIN(Score) FROM PetShow )
ORDER BY PetId ASC;

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 2       | Scratch   | 3       |
+---------+-----------+---------+

And in Oracle Database, we can do this:

SELECT
    PetId,
    PetName,
    Score
FROM PetShow 
WHERE Score = ( SELECT MIN(Score) FROM PetShow )
ORDER BY PetId ASC
FETCH FIRST 1 ROW ONLY;

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 2       | Scratch   | 3       |
+---------+-----------+---------+

Option 2

If we only want one row returned, we can actually do away with most of the other code and just get the first row out of the ordered results:

SELECT 
    PetId,
    PetName,
    Score
FROM PetShow 
ORDER BY Score ASC
LIMIT 1;

Result:

+-------+---------+-------+
| PetId | PetName | Score |
+-------+---------+-------+
|     7 | Punch   |     3 |
+-------+---------+-------+

In SQL Server:

SELECT TOP 1
    PetId,
    PetName,
    Score
FROM PetShow 
ORDER BY Score ASC;

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 2       | Scratch   | 3       |
+---------+-----------+---------+

And in Oracle Database:

SELECT
    PetId,
    PetName,
    Score
FROM PetShow 
ORDER BY Score ASC
FETCH FIRST 1 ROW ONLY;

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 2       | Scratch   | 3       |
+---------+-----------+---------+

Option 3

Another way to select the row with the minimum value is to join the table on itself, like this:

SELECT 
    p1.PetId, 
    p1.PetName, 
    p1.Score
FROM PetShow p1
LEFT JOIN PetShow p2 ON p1.Score < p2.Score
WHERE p2.PetId IS NULL;

Result:

+---------+-----------+---------+
| PetId   | PetName   | Score   |
|---------+-----------+---------|
| 1       | Wag       | 85      |
| 8       | Purr      | 85      |
+---------+-----------+---------+

As with the earlier example, we can limit the results to one row (or some other number) if required.

Функция MIN() в SQL возвращает минимальное значение поля из всех найденных строк, которые удовлетворяют вашему запросу.

Синтаксис MIN()

SELECT MIN(поле_таблицы)
FROM имя_таблицы
WHERE условие_для_поиска;

Примеры запросов

1. Вывести минимальную заработную заработную плату сотрудников

SELECT min(salary)
FROM workers;

2. Вывести минимальную заработную плату среди сотрудников женского пола

SELECT MIN(salary)
FROM workers
WHERE sex = 'Женский';

Метки: MIN, SQL, Математические функции.