In this post, I am giving you a full demonstration on finding top N records for each group in MySQL.
Today morning, I was working on one report and requirement is to find top 2 employee working hours for each employee.
I have tried many scripts and finally got the solution which is also forcing me to write a good post on this.
First, create sample data,
CREATE DATABASE Employee;
CREATE TABLE Employee.EmployeePivotTest
INSERT INTO Employee.EmployeePivotTest VALUES
The Second step is to find a top N working hour by each employee.
FROM Employee.EmployeePivotTest AS EP
ORDER BY EmpAvgWorkingHours DESC)
GROUP BY EmpName
) AS T
ON EP.EmpName = T.EmpName
AND FIND_IN_SET(EmpAvgWorkingHours,GroupedWorkingHours) <=2
ORDER BY EmpName,EmpDeptName;
Now see above result, each employee has two top 2 record base on working hours.
In above query I have used GROUP_CONCAT, and FIND_IN_SET.
GROUP_CONCAT groups column value into a single string and FIND_IN_SET is used to get the position of the string in a comma delimited string list.
You can change the value of 2 to N for your customized report.