This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE tbl_EmployeeDetails ( EmpID INTEGER PRIMARY KEY ,EmpName VARCHAR(50) ,EmpSalary BIGINT ,DepartmentName VARCHAR(50) ); INSERT INTO tbl_EmployeeDetails VALUES (1,'Anvesh',80000,'Sales') ,(2,'Neevan',90000,'Sales') ,(3,'Jenny',50000,'Production') ,(4,'Roy',60000,'Production') ,(5,'Martin',30000,'Research') ,(6,'Mahi',85000,'Research') ,(7,'Kruti',45000,'Research') ,(8,'Manish',75000,'Research'); |
Find max salary values for each department:
|
1 2 3 4 5 6 |
SELECT ED1.* FROM tbl_EmployeeDetails ED1 LEFT JOIN tbl_EmployeeDetails ED2 ON ED1.DepartmentName = ED2.DepartmentName AND ED1.EmpSalary < ED2.EmpSalary WHERE ED2.EmpSalary IS NULL |
The Result:
