In this post, I am giving you a full demonstration on finding max value for each group of records in MySQL.
Today morning, I was working on one report and requirement is to find max value records for each particular group of data.
I know, You guys are thinking that this is a very simple solution using MAX() and GROUP BY clause.
But many of database developers have always questioned about: Why we cannot add any other columns in SELECT list which are not part of aggregation or the GROUP BY clause?
Below is a one solution, you can find MAX record without using GROUP BY clause and you can also add other columns in the SELECT list.
First, Create a table with sample data:
CREATE TABLE tbl_EmployeeDetails
EmpID INTEGER PRIMARY KEY
INSERT INTO tbl_EmployeeDetails
Find max salary values for each department:
FROM tbl_EmployeeDetails ED1
LEFT JOIN tbl_EmployeeDetails ED2
ON ED1.DepartmentName = ED2.DepartmentName
AND ED1.EmpSalary < ED2.EmpSalary
WHERE ED2.EmpSalary IS NULL