Here are five options for using SQL to return only those rows that have the maximum value within their group.

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

Sample Data

Suppose we have a table with the following data:

SELECT * FROM Gameshow;

Result:

+--------------+--------+---------+
| Contestant   | Game   | Score   |
|--------------+--------+---------|
| Faye         | 1      | 85      |
| Faye         | 2      | 50      |
| Faye         | 3      | 63      |
| Jet          | 1      | 31      |
| Jet          | 2      | 40      |
| Jet          | 3      | 51      |
| Spike        | 1      | 25      |
| Spike        | 2      | 27      |
| Spike        | 3      | 15      |
+--------------+--------+---------+

And suppose that we want to get the highest score for each contestant.

Option 1

A quick and easy option is to construct a query with the SQL GROUP BY clause:

SELECT 
    Contestant,
    MAX( Score ) AS MaxScore
FROM Gameshow
GROUP BY Contestant
ORDER BY Contestant;

Result:

+--------------+------------+
| Contestant   | MaxScore   |
|--------------+------------|
| Faye         | 85         |
| Jet          | 51         |
| Spike        | 27         |
+--------------+------------+

Option 2

If we want to include the game that each contestant played to get the max score, then one way to do that is to use a correlated subquery like this:

SELECT 
    Contestant,
    Game,
    Score
FROM Gameshow g1
WHERE Score = ( SELECT MAX( g2.Score )
              FROM Gameshow g2
              WHERE g1.Contestant = g2.Contestant )
ORDER BY Contestant;

Result:

+--------------+--------+---------+
| Contestant   | Game   | Score   |
|--------------+--------+---------|
| Faye         | 1      | 85      |
| Jet          | 3      | 51      |
| Spike        | 2      | 27      |
+--------------+--------+---------+

Correlated subqueries refer to one or more columns from outside of the subquery. Correlated subqueries can be inefficient, mainly due to the fact that the subquery is executed repeatedly, once for each row that might be selected by the outer query. Correlated subqueries are also known as repeating subqueries.

Option 3

We can alternatively use an uncorrelated subquery like this:

SELECT 
    g1.Contestant, 
    g1.Game,
    g1.Score
FROM Gameshow g1
JOIN (
  SELECT Contestant, MAX( Score ) AS Score
  FROM Gameshow
  GROUP BY Contestant ) AS g2
  ON g1.Contestant = g2.Contestant AND g1.Score = g2.Score
ORDER BY Contestant ASC;

Result:

+--------------+--------+---------+
| Contestant   | Game   | Score   |
|--------------+--------+---------|
| Faye         | 1      | 85      |
| Jet          | 3      | 51      |
| Spike        | 2      | 27      |
+--------------+--------+---------+

Uncorrelated subqueries don’t depend upon the outer query for their execution. They can execute completely independently of the outer query.

In Oracle, we need to remove the AS when declaring the column aliases:

SELECT 
    g1.Contestant, 
    g1.Game,
    g1.Score
FROM Gameshow g1
JOIN (
  SELECT Contestant, MAX( Score ) Score
  FROM Gameshow
  GROUP BY Contestant ) g2
  ON g1.Contestant = g2.Contestant AND g1.Score = g2.Score
ORDER BY Contestant ASC;

Option 4

Another option is to use a LEFT JOIN, like this:

SELECT 
    g1.Contestant, 
    g1.Game,
    g1.Score
FROM Gameshow g1
LEFT JOIN Gameshow g2 ON 
    g1.Contestant = g2.Contestant AND g1.Score < g2.Score
WHERE g2.Contestant IS NULL
ORDER BY g1.Contestant ASC;

Result:

+--------------+--------+---------+
| Contestant   | Game   | Score   |
|--------------+--------+---------|
| Faye         | 1      | 85      |
| Jet          | 3      | 51      |
| Spike        | 2      | 27      |
+--------------+--------+---------+

Option 5

Another way to fetch rows with the maximum value in a given column is to use a common table expression with a window function:

WITH cte AS (
   SELECT Contestant, Game, Score,
            RANK() OVER ( PARTITION BY Contestant
            ORDER BY Score DESC
            ) AS r
    FROM Gameshow
)
SELECT Contestant, Game, Score
FROM cte
WHERE r = 1
ORDER BY Contestant ASC;

Result:

+--------------+--------+---------+
| Contestant   | Game   | Score   |
|--------------+--------+---------|
| Faye         | 1      | 85      |
| Jet          | 3      | 51      |
| Spike        | 2      | 27      |
+--------------+--------+---------+