Here are three examples that use SQL to find and select the row with the maximum value in a given column.

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

Sample Data

We’ll 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      |
| 7       | Punch     | 3       |
+---------+-----------+---------+

Option 1

Here’s an example of selecting the row with the maximum value from the Score column in the above table:

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

Result:

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

We used the MAX() function within a subquery to find the maximum value, and returned the whole row with the outer query.

When there are Multiple Rows with the Max Value

Using this method, if there’s more than one row with the max value, all of them are returned.

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

INSERT INTO PetShow VALUES (8, 'Purr', 85);
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       |
| 8       | Purr      | 85      |
+---------+-----------+---------+

We can see that both Wag and Purr have got the highest score of 85.

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

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

Result:

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

Both rows with the max values are returned as expected.

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 MAX(Score) FROM PetShow )
ORDER BY PetId ASC
LIMIT 1;

Result:

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

In SQL Server, we can use the TOP clause:

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

Result:

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

And in Oracle Database:

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

Result:

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

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 DESC
LIMIT 1;

Result:

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

In SQL Server:

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

Result:

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

And in Oracle Database:

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

Result:

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

Option 3

Another way to select the row with the maximum 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.