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 query requirement in relational databases is to get rows with the max value in a particular column. Here, we’ll look at different ways to do this, depending on different situations and database environments.

In this article, we will see SQL queries to select row with maximum value in one column.

Demo SQL Database

Create a table “Teacher” given below which will be used in the examples below:

Write the following SQL queries to create this table:

ID NAME	DEPARTMENT	SALARY
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

To create this table, write the following SQL queries:

SQL

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);

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 like:

Let’s discuss each of these methods in detail below:

1. Using Aggregate Function to Select Rows with Max Value

It is probably the easiest way to solve the problem. Here we’ll use the built-in aggregate function MAX.

Query:

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

Output:

Using Aggregate Function

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:

Using Keyword ALL

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:

Using Keyword NOT EXISTS

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.

Query:

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

Output:

Using NOT IN

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:

Using Set Operator MINUS

Explanation:

Like the previous subquery, we’ll find the row with a salary less than that of any other teacher’s salary. Then we will do the set difference operation of the table that we have found (Rows not having maximum salary) on Teacher table to find the complement of table which we found to find the rows having a 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:

Using Left Outer Join

Explanation:

Here, we will perform Left outer join (self join) on the Teacher table with the right table having more salary than the left table. All the rows not having maximum salary will contain at least one row on right having more than its salary in the left (i.e. row with maximum salary will be greater). But for the maximum row, there exists no such row. Due to the left join constraint, null will be assigned to the right table. So we are selecting the row of the left table having the right table as null to be the maximum salary row.

Conclusion

Selecting rows with the maximum value in a column is a common SQL task, and by utilizing effective sorting and column organization, you can enhance the readability and usability of your query results.

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.