3 Ways to Select the Row with the Minimum Value in SQL

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.