How to Select Row With Max Value in SQL?

SQL(Structured Query Language) is a powerful tool that is used to manage and manipulate data in a database. Sometimes you need to find the maximum value from a table of a column for each distinct value in another column. This task can be done using various techniques. 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.

To fetch the rows that have the Max value for a column for each distinct value of another column in SQL typically involves combining the rows with the help of the GROUP BY clause with aggregate functions such as MAX() which gives the maximum values of a column to achieve the desired results.

Syntax:

SELECT 
    <Distinct_Column>,
    MAX(<Max_Column>) AS Max_Value,
    <Other_Columns>
FROM 
    <Your_Table>
GROUP BY 
    <Distinct_Column>;

The technique involves:

• 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

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:

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;

This query groups the rows by the “department” column and calculates the maximum salary within each group using the MAX() function.

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. The result provides a concise summary, indicating the highest salary within each department.

Example 2: Using Nested Query

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

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

This query selects the student_id and score from the students table where the score matches the maximum score calculated for each 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. This provides a concise summary, indicating the highest score for each student.

Example 3: Using Join

Using the Join by Using the above Student Table:

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;

This query also retrieves the student_id and score from the students table where the score matches the maximum score calculated for each student_id, but it uses a join operation to achieve this.

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.

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.

How can I find the maximum value in a single column?

Use the MAX() function:

SELECT MAX(column_name) AS max_value
FROM table_name;

How do I get multiple columns along with the maximum value in a group?

Use a subquery with a join:

SELECT t1.*
FROM table_name t1
JOIN (
    SELECT column_name, MAX(another_column) AS max_value
    FROM table_name
    GROUP BY column_name
) t2 ON t1.column_name = t2.column_name AND t1.another_column = t2.max_value;

Why use the GROUP BY clause for finding maximum values?

The GROUP BY clause groups rows with the same values, allowing the MAX() function to find the maximum value within each group.

What is the theoretical basis for using subqueries or joins to fetch additional columns with the maximum value?

Subqueries or joins combine aggregated results with original data, leveraging relational algebra to ensure accurate retrieval of additional columns along with maximum values.