How to Select Row With Max Value in MySQL?
Last Updated :
05 Feb, 2024
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 clauseExplanation:
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 tablesExplanation:
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.
Similar Reads
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 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 c
5 min read
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 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 Random Row in MySQL
In database operations, selecting random rows from a table is a common requirement for various applications, such as gaming, content recommendation, and statistical sampling. In this article, we learn different methods for selecting random rows in MySQL. We'll understand various approaches, includin
5 min read
How to SELECT Rows With MAX PARTITION By Another Column in MySQL
MySQL is a widely used relational database management system (RDBMS) that provides a robust and scalable platform for managing and organizing data. MySQL is an open-source software developed by Oracle Corporation, that provides features for creating, modifying, and querying databases. It utilizes St
6 min read
Show All Rows with an Above-Average Value in MySQL
Finding All Rows with an Above-Average Value in MySQL is easy because in this article we will learn some methods to identify rows in a dataset where values exceed the dataset's average. Using MySQL we will discuss two approaches using subqueries with average calculations and through JOIN operations
4 min read
Select row with maximum and minimum value in Pandas dataframe
Let's see how can we select rows with maximum and minimum values in Pandas Dataframe with help of different examples using Python. Creating a Dataframe to select rows with max and min values in Dataframe C/C++ Code # importing pandas and numpy import pandas as pd import numpy as np # data of 2018 dr
2 min read
How to Fetch the Rows Which have the Max Value for a Column in PL/SQL?
In PL/SQL, retrieving rows that contain the maximum value for a column is a common task. This process involves identifying the highest value for a specific column within each group defined by another column. In this article, we will learn how to achieve this using various approaches in PL/SQL with t
5 min read