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

1. Group records by the distinct column that contains the categories.
   
2. Find the maximum value in the other column per group.
   
3. 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

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

Explanation:

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

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

Explanation:

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

Explanation:

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.