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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE DATABASE Employee; CREATE TABLE Employee.EmployeePivotTest ( EmpName VARCHAR(255) ,EmpDeptName VARCHAR(255) ,EmpAvgWorkingHours INTEGER ); INSERT INTO Employee.EmployeePivotTest VALUES ('Anvesh','Computer-IT',226) ,('Anvesh','Computer-IT',100) ,('Anvesh','Computer-IT',200) ,('Anvesh','Computer-IT',752) ,('Anvesh','Account',142) ,('Anvesh','Marketing',110) ,('Anvesh','Finance',236) ,('Anvesh','Account',120) ,('Neevan','Computer-IT',120) ,('Neevan','Finance',852) ,('Neevan','Account',326) ,('Neevan','Marketing',50) ,('Neevan','Finance',140); |
Find a top N working hour by each employee:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT EP.EmpName ,EmpDeptName ,EmpAvgWorkingHours FROM Employee.EmployeePivotTest AS EP INNER JOIN ( SELECT EmpName ,GROUP_CONCAT(DISTINCT EmpAvgWorkingHours ORDER BY EmpAvgWorkingHours DESC) AS GroupedWorkingHours FROM Employee.EmployeePivotTest GROUP BY EmpName ) AS T ON EP.EmpName = T.EmpName AND FIND_IN_SET(EmpAvgWorkingHours,GroupedWorkingHours) <=2 ORDER BY EmpName,EmpDeptName; |
Result:
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.
