How to Select Row With Max Value on a Column in SQL?
Last Updated :
04 Dec, 2024
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
Techer Table1. 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
Select Row with Max value Example1Explanation:
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
ID | Name | Department | Salary |
---|
103 | Ambika | Computer Science | 95000 |
107 | Simon | Mathematics | 95000 |
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
ID | Name | Department | Salary |
---|
103 | Ambika | Computer Science | 95000 |
107 | Simon | Mathematics | 95000 |
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
ID | Name | Department | Salary |
---|
103 | Ambika | Computer Science | 95000 |
107 | Simon | Mathematics | 95000 |
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
ID | Name | Department | Salary |
---|
103 | Ambika | Computer Science | 95000 |
107 | Simon | Mathematics | 95000 |
Explanation:
- The subquery retrieves all rows where the salary is less than another salary.
- 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
ID | Name | Department | Salary |
---|
103 | Ambika | Computer Science | 95000 |
107 | Simon | Mathematics | 95000 |
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.
Similar Reads
How to Select Row With Max Value in SQL?
SQL(Structured Query Language) is a powerful tool that is used to manage and query data in relational databases. A common requirement in data analysis is finding the maximum value in a column for each distinct value of another column, such as determining the highest salary in each department. This c
5 min read
How to Select Row With Max Value in in SQLite
In SQLite, retrieving rows with the maximum value for a specific column, grouped by another column's distinct values can be a challenging task. Whether you're analyzing data trends or identifying top performers, this operation can provide valuable output. In this beginner-friendly guide, we will exp
4 min read
How to Select Row With Max Value in SQL Server
In SQL Server, retrieving rows that contain the maximum value for a specific column for each distinct value in another column can be a common and challenging task. This process is done by identifying the maximum value for each group and then selecting the corresponding rows. In this article, we'll e
6 min read
How to Select Row With Max Value in MySQL?
MYSQL is an open-source Relation Database Management System that stores data in tables with rows and columns. It is formed from two words â âMyâ and âSQLâ. âMyâ is the name of one of the co-founders Michael Widenessâs daughter and âSQLâ stands for Structured Query Language. MySQL is written in C and
4 min read
How to Select Row With Max Value in PostgreSQL
In PostgreSQL, efficiently selecting rows with maximum values from a table is a common task faced by developers and database administrators. Whether you're working on analytics, reporting, or data manipulation, knowing how to retrieve the maximum value per group can significantly enhance your SQL sk
4 min read
How to SELECT Rows With MAX PARTITION By Another Column in MySQL
MySQL is a widely used relational database management system (RDBMS) that provides a robust and scalable platform for managing and organizing data. MySQL is an open-source software developed by Oracle Corporation, that provides features for creating, modifying, and querying databases. It utilizes St
6 min read
How to select min and max from table by column score in SQLAchemy?
In this article, we are going to fetch min and max values from column scores in the SQL table using the SQLAlchemy module. SQLAlchemy is an awesome and easy-to-use python module to connect with SQL and use the features and powers of SQL in python. Here, we are provided with a table that has a column
3 min read
How to Fetch the Rows Which have the Max Value for a Column in PL/SQL?
In PL/SQL, retrieving rows that contain the maximum value for a column is a common task. This process involves identifying the highest value for a specific column within each group defined by another column. In this article, we will learn how to achieve this using various approaches in PL/SQL with t
5 min read
How to Select the Nth Row in a SQLite Database Table?
In SQLite, selecting a specific row from a table can be a common requirement, especially when dealing with large datasets. In this article, we will explore different methods to select the nth row from a SQLite database table. Whether we're a beginner or an experienced developer understanding these m
4 min read
How to Find Most Frequent Value in Column in SQL?
To find the most frequent value in a column in SQL, use the COUNT() function to get a count of each unique value, sort the result in descending order, and select the first value in the final results set. In SQL, sometimes we need to find frequent values in a column. Finding the Most Frequent Value i
1 min read