MySQL: Select all duplicate MIN from counted group

March 4, 2022

I have this example data and I wanted to query the employee having the lowest count of attendance.

employee_id	employee	today	time_in	time_out
1	JUAN	2022-03-04	07:23:43	05:23:11
1	JUAN	2022-03-03	07:05:43	05:06:21
1	JUAN	2022-03-02	07:12:01	05:32:21
2	BEN	2022-03-04	07:17:01	05:36:21
3	WENDY	2022-03-04	07:23:43	05:23:11
3	WENDY	2022-03-03	07:05:43	05:06:21
3	WENDY	2022-03-02	07:12:01	05:32:21
4	JOHN	2022-03-02	07:54:01	05:02:42

I am able to get the total of how many times their attendance is for a given date range:

SELECT COUNT(*) in_count, employee_id, employee
FROM active_attendance
WHERE (today BETWEEN  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )
GROUP BY employee_id
ORDER BY in_count ASC

And this yields to:

in_count	employee_id	employee
1	2	BEN
1	4	JOHN
3	1	JUAN
3	3	WENDY

Now I’m struggling how I would filter only the LOWEST in_count in this scenario I want only BEN & JOHN to be returned.

How I want it to be:

in_count	employee_id	employee
1	2	BEN
1	4	JOHN

What I’ve tried: This only returns the first MIN value and not including the duplicate

SELECT MIN(att), employee_id, employee 
FROM (
    SELECT COUNT(*) in_count, employee, employee_id
    FROM 202_active_attendance
    WHERE  today BETWEEN '2021-04-01' AND '2021-04-30'
    GROUP BY employee_id
    ORDER BY in_count
) AS test

>Solution :

On MySQL 8+, we can use the RANK analytic function:

WITH cte AS (
    SELECT COUNT(*) in_count, employee_id, employee,
           RANK() OVER (ORDER BY COUNT(*)) rnk
    FROM active_attendance
    WHERE today BETWEEN  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()
    GROUP BY employee_id
)

SELECT in_count, employee_id, employee
FROM cte
WHERE rnk = 1;