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 C++ programming languages. It supports Linux, Solaris, macOS, Windows, and FreeBSD operating systems. It supports SQL language for querying and managing data. In MySQL, data is stored in tables. A table consists of columns (attribute) and rows (value). Each column is associated with the data type of the attribute. It allows operations like inserting, updating, deleting, and querying data. It supports DDL, DML, DCL, and TCL languages.

In this article, we are going to see how to Fetch the rows that have the Max value for a column for each distinct value of another column in MySQL.We need to find the row with the max value in one column for each distinct value in another column.

Rows with Max Value for Each Distinct Category

As per the scenario, we need to get the max values from the column that has a distinct value in another column. The table should have at least two columns for the operation. To find the max value, the max() function is used, and to get a distinct value for the column we apply the clause to the column It is possible that for each distinct value from a column in A table, we need to fetch the max value from another column of B table which are related to each other.

Example: Consider the “instructor” table with (T_Id, name,dept_name, salary) fields.For each distinct department name find the max salary of the instructor.

Syntax:

CREATE DATABASE database_name; // to create a database

use database_name; // to use a database

CREATE TABLE table_name(attributes and their datatypes)

desc table_name // to describe the table

INSERT INTO table_name(values for each attribute)

Example of Rows with Max Value for Each Distinct Category

In the ‘instructor’ table within the ‘GeksforGeeks’ database, you can see that the SQL query determines the highest salary for each department. This gives you a clear view of the top salary for each department, making it easier to analyze the data.

Example 1: Retrieving Maximum Salary for Each Department in the ‘instructor’

CREATE DATABASE GeeksforGeeks;          
use GeeksforGeeks;                       

CREATE TABLE instructor (T_ID INT PRIMARY KEY,name VARCHAR(20),dept_name VARCHAR(20),salary int);

desc instructor;

INSERT INTO instructor (T_ID, name, dept_name, salary) VALUES(101, 'Amol', 'Computer', 45000),
                                          (102, 'Amit', 'ENTC', 55000),
                                           (103, 'Anil', 'Computer', 48000),
                                             (104, 'Om', 'ENTC', 42000),
                                             (105, 'Ajay', 'MECH', 46000);

Explanation:

• Initially, we have created a database as GeeksforGeeks and we use it to perform the required task.
• In next steps a table named instructor is created which contain 4 attributes and is described using desc statement.
• 5 rows are inserted into the according to the attributes.

Fetch the rows which have the Max value for a column for each distinct value of another column in MySQL

Syntax:

SELECT column1,MAX( column2) FROM table_name GROUP BY column2;

 SELECT dept_name, MAX(salary) FROM instructor GROUP BY dept_name;

Output:

Result using Groupby clause

Explanation:

The instructor table contains 4 attributes,(T_ID, name, dept_name, salary).For each distinct value of dept_name, max value of salary is requested. As there are 3 distinct dept_name,3 max value for the salary are displayed.

Example 2: Retrieving Maximum Customer Amount for Each Account Type

Fetch the max value from a column of A table for each distinct value of another column of B table.

use GeeksforGeeks;

CREATE TABLE Customer (c_id INT, c_name VARCHAR(20), amount DECIMAL(7, 2));
CREATE TABLE Account (c_id INT, acc_type VARCHAR(20));

DESC Customer;
DESC Account;

INSERT INTO Customer (c_id, c_name, amount) VALUES (1, 'Ram', 5555.00), (2, 'Om', 66488.00), (3, 'Ajay', 24589.00), (4, 'Atul', 25469.00),
(5, 'Aniket', 6452.00), (6, 'Aditya', 7582.00);
INSERT INTO Account (c_id, acc_type) VALUES (1, 'Student'), (2, 'Saving'), (3, 'Current'), (4, 'Student'), (5, 'Saving'), (6, 'Current');;

SELECT  Account.acc_type, MAX(Customer.amount) FROM Customer JOIN Account ON Customer.c_id = Account.c_id GROUP BY Account.acc_type;

Output:

Fetch result from two tables

Explanation:

In this example,Two tables i.e. Account and Customer are used.For each distinct account type in the account table,maximum account from the Customer table is returned. To return the result SELECT statement along with the JOIN is used.

Conclusion

In MySQL, you can get rows with the max value for a particular column within each unique value of another column. This improves your data analysis. You can easily find top entries within different categories. By using SQL queries that include grouping and aggregation functions, like MAX(), data summary becomes quick and easy, which helps you make better decisions and a more organized database.