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
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.