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/
