How to Select Row With Max Value in SQL?
Last Updated :
04 Dec, 2024
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 can be achieved through GROUP BY, aggregate functions like MAX(), nested queries, or JOINs.
In this article, we see the basic concept to fetch the rows with maximum value and also different examples to find rows with maximum value, demonstrating multiple techniques for different scenarios.
Rows with Max Value for Each Distinct Category
To fetch rows with the maximum value in one column for each unique value in another column, we can use the GROUP BY clause with the MAX() function. This groups the data by the distinct column and finds the highest value in the specified column for each group.
Syntax
SELECT
<Distinct_Column>,
MAX(<Max_Column>) AS Max_Value,
<Other_Columns>
FROM
<Your_Table>
GROUP BY
<Distinct_Column>;
Key Concepts: Rows with Maximum Values for Each Category
- Group records by the distinct column that contains the categories.
- Find the maximum value in the other column per group.
- Join the aggregated result with the original table to fetch other columns.
Example 1: Using the MAX function
In this example, we create an Employee table and use the MAX() function with the GROUP BY clause to find the highest salary in each department. This approach efficiently summarizes the data, showing the maximum salary for each distinct department.
1. Creating employee table
CREATE TABLE employee (
employee_id INT,
department VARCHAR(255),
salary INT
);
INSERT INTO employee (employee_id, department, salary) VALUES
(1, 'IT', 50000),
(2, 'IT', 60000),
(3, 'HR', 55000),
(4, 'HR', 52000),
(5, 'SALES', 48000),
(6, 'SALES', 51000);
Output
employee_id | department | salary |
---|
1 | IT | 50000 |
2 | IT | 60000 |
3 | HR | 55000 |
4 | HR | 52000 |
5 | SALES | 48000 |
6 | SALES | 51000 |
2. Fetch Maximum Salary Per Department
To fetch the rows with the maximum salary for each department, we can use the following SQL query:
SELECT department, MAX(salary) AS max_salary
FROM employee
GROUP BY department;
Output
OutputExplanation:
The SQL query selects the maximum salary (max_salary) for each distinct department from the "employee" table, grouping the data by the "department" column and calculates the maximum salary within each group using the MAX() function.
Example 2: Using Nested Query
In this example, we create a Student table and use a nested query to find the highest score for each student. The inner query calculates the maximum score for each student ID, and the outer query retrieves rows that match these maximum scores, providing detailed results for each student.
1. Creating Student table
CREATE TABLE Student (
student_id INT,
score INT
);
INSERT INTO Student (student_id, score) VALUES
(1, 90),
(1, 85),
(2, 70),
(2, 85),
(3, 69),
(3, 95);
Output
student_id | score |
---|
1 | 90 |
1 | 85 |
2 | 70 |
2 | 85 |
3 | 69 |
3 | 95 |
2. Fetch Maximum Score Per Student
To fetch the rows with the maximum salary for each department, we can use the following SQL query:
SELECT s.student_id, s.score
FROM student s
WHERE s.score = (
SELECT MAX(score)
FROM student
WHERE student_id = s.student_id
);
Output
OutputExplanation:
The SQL query retrieves rows from the "Student" table, showcasing the student_id and score for each student, filtering only those where the score matches the maximum score for the corresponding student_id.
Example 3: Using Join
In this example, we use a JOIN to fetch the maximum score for each student from the Student table. A subquery calculates the maximum score grouped by student ID, and the main query joins it with the original table to retrieve the student ID and corresponding maximum score.
Fetch Maximum Score Using JOIN
This query uses a subquery to calculate the maximum score
for each student_id
. It then joins the subquery results with the original student
table to fetch rows that match the maximum scores.
SELECT s.student_id, s.score
FROM student s
JOIN (
SELECT student_id, MAX(score) AS max_score
FROM student
GROUP BY student_id
) AS max_scores
ON s.student_id = max_scores.student_id
AND s.score = max_scores.max_score;
Output
OutputExplanation:
The SQL query selects the student_id and score from the "Student" table, joining it with a subquery that calculates the maximum score for each student_id. The result displays rows where the score matches the maximum score for each corresponding student_id, providing a concise summary of top scores for each student.
Conclusion
Fetching rows with maximum values for a column within each distinct value of another column in SQL involves leveraging the GROUP BY clause and aggregate functions like MAX(). We also get the maximum values from rows using nested queries and using Join. Understanding and mastering these SQL techniques can greatly enhance your ability to extract meaningful insights from your data.
Similar Reads
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 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
5 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 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 Limit Rows in a SQL Server?
To limit rows in SQL Server, use the TOP clause in the SELECT statement. Using the TOP clause in SQL Server, users can limit the number of rows in the results set. Here, we will understand how to limit rows in SQL Server with the help of different examples. Steps to Limit Rows in SQL ServerLet's che
3 min read
How to Select the nth Row in a SQL Server Database Table?
In SQL Server databases, it's common to encounter scenarios where we need to retrieve a specific row, such as the nth row, from a table efficiently. Whether you're building a pagination feature for a web application or analyzing data, having the ability to select a particular row based on its positi
3 min read
How to Select the Top 10 Rows From a Table in MariaDB
In the data managing systems, MariaDB stands as a robust and adaptable MariaDB stands as a robust and adaptable choice in data managing systems. It's known for efficiently retrieving data with lots of tools and instructions. We're looking at the SELECT TOP clause, a key part of fetching data from
5 min read
Show All Rows with an Above-Average Value in SQL
In SQL, finding All Rows of an Above Average Value is simple and is retrieved by the AVG() Function. There are various methods available in SQL that help us to easily find the Average value. In this guide, we will learn about various methods with detailed examples and their output. Show All Rows wit
4 min read