MySQL limit 5 per month

user_5934330 0
Viewed 187

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?

mysql-workbench mysql
1 Answers

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
user_24693080
Related