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.

In SQL, records have a maximum value for every unique value in a column. For example, if you want to find the last entry in each category in a dataset, you need to find the latest entry in each column. In this article, we’ll show you how to use SQL queries to find the most recent entry in a dataset.

Rows with Max Value for Each Distinct Category

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 varchar2,salary int)

employee_id	department	salary
1	IT	50000
2	IT	60000
3	HR	55000
4	HR	52000
5	SALES	48000
6	SALES	51000

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

student_id	score
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 students s
WHERE s.score = (
    SELECT MAX(score)
    FROM students
    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

Creating Student table

Create table Student(student_id int,score int);

student_id	score
1	90
1	85
2	70
2	85
3	69
3	95

SELECT s.student_id, s.score
FROM students s
JOIN (
    SELECT student_id, MAX(score) AS max_score
    FROM students
    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.

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.