Find top N records for each group in MySQL

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.