Dfyjkt

I try to show the 'top 5' per month of worked hours.

I have the following query:

SELECT
 concat(m.firstname, " ",m.lastname) AS name,
 SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(pl.end_activity,pl.start_activity)))) AS activity,
 month(start_activity) AS month,
 year(start_activity) AS year
FROM 
 log AS pl
INNER JOIN
 employee AS m
ON
 m.employee = pl.employee
GROUP BY
 name,
 year,
 month,
ORDER BY
 year,
 month,
 activity

I tried: limit 0,5 bit it gives me only the first 5 records of all. How can I show 5 records ordered by month?

In MySQL version 8.0.2 and above, we can utilize Window Functions. We can utilize Row_Number() window function to determine row numbers within a partition of concatenated expression of year and month. Ordering within the partition is done based on the descending order of activity.

We can then use this result-set as a Derived Table, and consider row number up-to 5. This will give us 5 rows per month, having top activity values.

SELECT dt.* 
FROM 
(
 SELECT
 concat(m.firstname, " ",m.lastname) AS name,
 SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(pl.end_activity,pl.start_activity)))) AS activity,
 month(start_activity) AS month,
 year(start_activity) AS year, 
 ROW_NUMBER() OVER (PARTITION BY CONCAT(year(start_activity), month(start_activity))
 ORDER BY SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(pl.end_activity,pl.start_activity)))) DESC) AS row_no 
 FROM 
 log AS pl
 INNER JOIN
 employee AS m
 ON
 m.employee = pl.employee
 GROUP BY
 name,
 year,
 month
) AS dt 
WHERE dt.row_no <= 5
ORDER BY
 dt.year,
 dt.month,
 dt.activity