How to Select Only Rows With Maximum Value in a Column in SQL

1. Overview

In SQL, selecting rows with the maximum value in a specific column is a common requirement. Whether working with large datasets or generating reports, identifying the highest values within the data is essential for insightful analysis. SQL offers several techniques to accomplish this task, each with advantages and use cases.

In this tutorial, we’ll explore three effective methods to select only rows with the maximum value in a column.

Notably, we perform this operation in the Postgres database management system using the Teaching and Course table in the Baeldung University database schema. However, the same process applies to other SQL systems as well.

2. Using the MAX() Function

One straightforward method to select only rows with the maximum value in a column is utilizing the SQL MAX() function. This aggregate function calculates the maximum value of a specified column across all rows in a table. By incorporating the MAX() function into the query, we can filter the dataset to retrieve only the rows that contain this maximum value.

To select only the rows with the maximum credits in the Course table, we use the MAX() function:

SELECT *
 FROM Course
 WHERE credits = (SELECT MAX(credits) FROM Course);
+-------+---------------------------------------------+-----------+---------+-----------+---------------+
| id    | name                                        | textbook  | credits | is_active | department_id |
|-------+---------------------------------------------+-----------+---------+-----------+---------------|
| CS111 | Introduction to Operating Systems           | OS by ... | 7       | Yes       | 1             |
| CS112 | Introduction to Real Time Operating Systems | Real-T... | 7       | Yes       | 1             |
| CS113 | Introduction to Computer Architecture       | Comput... | 7       | Yes       | 1             |
| CS211 | Operating Systems: Intermediate             | OS by ... | 7       | Yes       | 1             |
| CS212 | Real Time Operating Systems: Intermediate   | Real-T... | 7       | Yes       | 1             |
...

In the query, we first calculate the maximum value of the credits column using a subquery with the MAX() function. Then, we select all rows from the Course table where the credits match the maximum value. The result of this query shows the rows with the maximum credits.

Thus, we effectively retrieve only the rows with the top credits amount from the dataset.

3. Using ORDER BY and LIMIT

Another method to select only rows with the maximum value in a column involves the ORDER BY clause in combination with the LIMIT clause. This approach sorts the rows based on the target column in descending order and then retrieves the top row or rows.

Continuing with the Course table from the previous section, we want to select only the rows with the highest credits. So, let’s retrieve only the rows with the highest credits using ORDER BY and LIMIT:

SELECT *
 FROM Course
 ORDER BY credits DESC
 LIMIT 1;

+------+------------------------------------+-----------------+---------+-----------+---------------+
| id   |               name                 |    textbook     | credits | is_active | department_id |
+------+------------------------------------+-----------------+---------+-----------+---------------+
| CS111 | Introduction to Operating Systems | OS by Tanenbaum |       7 | Yes       |             1 |
-------+------------------------------------+-----------------+---------+-----------+---------------+
(1 row)

This query sorts the rows by the credits column in descending order and then limits the result to only one row. However, even if there are multiple rows with the same maximum value, this approach only returns one of them.

4. Using JOIN

The third method involves a JOIN to select only the rows with the maximum value in a column. This approach is particularly useful when we need to compare values across multiple tables or when dealing with complex queries.

4.1. Example

Using the Course table, we can write a query to get the rows with the maximum credits via an INNER JOIN:

SELECT Course.*
FROM Course
INNER JOIN (
    SELECT MAX(credits) AS max_credits
    FROM Course
) AS MaxCredits ON Course.credits = MaxCredits.max_credits;

In this query, we first create a subquery (MaxCredits) that calculates the maximum value of the credits column. Then, we perform an INNER JOIN between the Course table and this subquery, selecting only the rows from Course that match the maximum credits.

4.2. Handling Multiple Tables

If we need to compare values across multiple tables, we can extend this approach.

To demonstrate, we can write a query to find the course with the maximum credits and its associated teaching roles in the Teaching table from the Baeldung University schema:

SELECT Course.*, Teaching.role, Teaching.semester, Teaching.year
 FROM Course
 INNER JOIN (
     SELECT MAX(credits) AS max_credits
     FROM Course
 ) AS MaxCredits ON Course.credits = MaxCredits.max_credits
 LEFT JOIN Teaching ON Course.id = Teaching.course_id;
+-------+-----------------+-----------+---------+-----------+---------------+------------+----------+------+
|    id |      name       |  textbook | credits | is_active | department_id |    role    | semester | year |
+-------+-----------------+-----------+---------+-----------+---------------+------------+----------+------|
| CE111 | Introduction... | Struct... |       7 | Yes       |             4 | Instructor | SPRING   | 2022 |
| CE111 | Introduction... | Struct... |       7 | Yes       |             4 | TA         | SPRING   | 2022 |
| CE121 | Geotechnical... | Introd... |       7 | Yes       |             4 | Instructor | SPRING   | 2022 |
| CE121 | Geotechnical... | Introd... |       7 | Yes       |             4 | TA         | SPRING   | 2022 |
| CE131 | Mechanics of... | Mechan... |       7 | Yes       |             4 | Instructor | SPRING   | 2022 |
| CE131 | Mechanics of... | Mechan... |       7 | Yes       |             4 | TA         | SPRING   | 2022 |
| CE141 | Mechanics of... | Mechan... |       7 | Yes       |             4 | Instructor | SPRING   | 2022 |
...
(437 rows)

This query includes all courses with the maximum credits and also provides teaching details where available. If teaching details are unavailable for a particular course, the teaching columns have no values.

5. Conclusion

In this article, we’ve explored three effective methods to select only rows with the maximum value in a column in SQL: using the MAX() function, using ORDER BY and LIMIT, and using a JOIN.

To summarize, each method provides a unique approach to achieving this common task, offering flexibility for different scenarios. With these techniques, we can efficiently query and analyze data to gain valuable insights.