This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration on finding top N records for each group in MySQL.
Today morning, I was working on one of the report and requirement was to find top 2 employee working hours for each department.
I tried many scripts and finally got the solution.
Create a table with sample data:
CREATE DATABASE Employee;
CREATE TABLE Employee.EmployeePivotTest
INSERT INTO Employee.EmployeePivotTest VALUES
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 check the above result, each employee has two top 2 records based on their working hours.
In the above query, I used GROUP_CONCAT and FIND_IN_SET.
GROUP_CONCAT groups the 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.