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.
FROM Employee a
SELECT max(salary) FROM Employee c
GROUP BY c.role
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;
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: