MySQL: How to get Max Value for each group?

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: