How to Select Row With Max Value on a Column in SQL?

SQL is a powerful language for managing and handling relational databases. A common requirement in database management is to retrieve rows where a specific column has the maximum value. Here, we will look at different ways to do this, depending on different situations and database environments. This task is common in scenarios such as identifying the highest-paid employee in an organization, the top-scoring student in a class, or the most expensive product in a catalog.

In this article, we will explain multiple SQL methods to select rows with the maximum value in a specific column. These methods range from simple aggregate functions to more advanced techniques like subqueries and joins

Methods to Select Rows with Maximum Value in a Column in SQL

There are several methods to select rows with maximum value in a column in SQL. To demonstrate these concepts, we will use a sample table named Teacher. This table contains details about teachers, their departments, and salaries. Below is the schema and data for the table:

Create and insert values in Teacher Table

CREATE TABLE Teacher (
    ID INT PRIMARY KEY,
    NAME VARCHAR(255),
    DEPARTMENT VARCHAR(255),
    SALARY DECIMAL(10, 2)
);

INSERT INTO Teacher (ID, NAME, DEPARTMENT, SALARY)
VALUES
    (101, 'Ben', 'Computer Science', 80000),
    (102, 'Harish', 'Mathematics', 64000),
    (103, 'Ambika', 'Computer Science', 95000),
    (104, 'Anitha', 'Physics', 82000),
    (105, 'Alice', 'Chemistry', 75000),
    (106, 'Mary', 'Mathematics', 50000),
    (107, 'Simon', 'Mathematics', 95000),
    (108, 'John', 'Economics', 75000);

Select * FROM Teacher;

Output

1. Using Aggregate Function to Select Rows with Max Value

This is one of the simplest methods to find rows with the maximum value in a column using the MAX() aggregate function.

Query:

SELECT * FROM Teacher
WHERE Salary = (
    SELECT MAX(Salary) FROM Teacher
);

Output

Explanation:

First, the subquery will be evaluated, which will find the maximum salary given to any teacher. And then the actual query will find details of the teachers having that maximum salary.

2. Using Keyword ALL to Select Rows with Max Value

To select rows where a specific column value is greater than all values in the same column using the ALL keyword in SQL:

Query:

SELECT * FROM Teacher
WHERE Salary >= ALL (
    SELECT Salary FROM Teacher
);

Output

Explanation:

The inner query will return the salary of all the teachers. Maximum salary will be greater than equal to all other salaries, by definition of maximum. And the outer query will find the teacher having that salary.

3. Using Keyword NOT EXISTS to Select Rows with Max Value

Here we will use the modified definition of maximum - 'Maximum Salary will not be less than any other salary'.

Query:

SELECT * FROM Teacher t1
WHERE NOT EXISTS (
    SELECT * FROM Teacher t2
    WHERE t1.Salary < t2.Salary
);

Output

Explanation:

In this query, we are using Correlated Subqueries. i.e. For each row in the table, we will find the other rows whose salary is greater than the current rows. The row having no other rows whose salary is greater than it is the maximum row.

4. Using NOT IN to Select Rows with Max Value

Its logic is similar to the previous query but simpler to understand as the subquery is independent and not correlated. This approach uses a simpler subquery that retrieves all non-maximum values and filters them out using NOT IN.

Query

SELECT * FROM Teacher
WHERE Salary NOT IN (
    SELECT t1.Salary
    FROM Teacher t1, Teacher t2
    WHERE t1.Salary < t2.Salary
);

Output

Explanation:

Firstly, in the subquery, we'll find the salary which is less than that of any other teacher's salary. And then we'll find the teacher who doesn't have a salary which is not maximum.

5. Using Set Operator MINUS to Select Rows with Max Value

To retrieve unique rows from the first query that do not appear in the second query using the MINUS set operator in SQL:

Query

SELECT * FROM Teacher
MINUS
SELECT t1.* FROM Teacher t1, Teacher t2
WHERE t1.Salary < t2.Salary;

Output

Explanation:

1. The subquery retrieves all rows where the salary is less than another salary.
   
2. The MINUS operator removes these rows from the main table, leaving only rows with the maximum salary.

6. Using Left Outer Join to Select Rows with Max Value

To retrieve all records from the left table and matching records from the right table using a LEFT OUTER JOIN in SQL:

Query

SELECT t1.* FROM Teacher t1
LEFT JOIN Teacher t2
ON t1.Salary < t2.Salary
WHERE t2.Salary IS NULL;

Output

Explanation:

• A left join is performed between two copies of the Teacher table.
  
• Rows in t1 that do not have a matching higher salary in t2 are retained (t2.Salary IS NULL).
  
• This ensures only rows with the maximum salary are returned.

Conclusion

Selecting rows with the maximum value in a column is a common SQL requirement. Each of these methods provides a unique way to achieve the result, whether by using aggregate functions, correlated subqueries, set operators, or joins. Choose the method that best fits our database environment and query requirements. This article covered 6 methods to select rows with maximum value in a column in SQL. Each method is explained is with an example to provide better understanding of the concept.