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)

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.