Avoid subquery get records with max value for each group of grouped mySQL results

How do you get the rows that contain the max value for each grouped set. I faced issue many times but I ended with following solution of subquery and two queries and using one resultset result in another query.

Here is data of Employee table. I want max salary role based..users
Employee table Data:

Name	Salary	Role
David	130,000	Software Engineer
John	140,000	DevOps Engineer
Bob	120,000	Software Engineer
Sarah	130,000	DevOps Engineer
Alice	110,000	Software Engineer
Steve	95,000	DevOps Engineer

Old query which takes too much time and memory.

SELECT *
FROM Employee a
JOIN
(
SELECT max(salary) FROM Employee c
GROUP BY c.role
) b
ON a.ID = b.ID
ORDER BY book_count DESC LIMIT 1

Finally I found this query which solved my problem. following is correct query. I used this type query with 8CR rows..It works perfectly fine..But you need understand this.. Try to explain query before using it and do proper indexing…on column which are used in left outer join….

SELECT emp1.*, emp2.ID FROM
Employee AS emp1
LEFT OUTER JOIN Employee AS emp2 ON emp2.role = emp1.role AND emp2.salary > emp1.salary
WHERE emp2.salary IS NULL;

query result:

David	130,000	Software Engineer	NULL
John	140,000	DevOps Engineer	        NULL

Please note – for this solution to work, you need to make sure you have the correct index in place. In this example, you’ll need to create an index that includes the role and salary columns in order to avoid full table scans.

 

I found following articles Useful:

https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Add a Comment

Your email address will not be published. Required fields are marked *