Select complete record with earliest timestamp on a day for each employee

Question

I have a table that stores facial login data of employees based upon employee id. I need to get the earliest login for each employee on a day and all other logins to be ignored. I know how to get latest or earliest record for each employee but I am unable to figure out how to get earliest entry in each day by each employee.

+----+-----------+--------------------------------------+-------------+-----------------------+
| id | camera_id |              image_name              | employee_id |      created_at       |
+----+-----------+--------------------------------------+-------------+-----------------------+
| 10 |         2 | pjcc7vf142pec6li7k8kqxuqvnmhm0tyo8ib |          16 | 2020-07-11 10:40:20   |
| 11 |         2 | 9iizfdtk3m81a745ut7tzqzqh8kf9ipz2u02 |           2 | 2020-07-11 10:40:22   |
| 14 |         2 | 3p74yrq35nfaazwdo8auguvn2h5hpugtfvvw |           2 | 2020-07-11 12:07:24   |
| 15 |         2 | hpa2am40ufke7o7q2y733hh83h7ykxxdgkof |          16 | 2020-07-11 12:09:35   |
| 16 |         2 | g7adgyzloab2t4z7xx2id0a9cjqx8ojfni99 |           2 | 2020-07-11 12:09:41   |
| 17 |         2 | tapufkiuj5toxfdoikjicbe3k7tl32yj5khp |          16 | 2020-07-12 12:09:47   |
| 18 |         2 | pjcc7vf142pec6li7k8kqxuqvnmhm0tyo8ib |          16 | 2020-07-12 14:40:20   |
| 19 |         2 | 9iizfdtk3m81a745ut7tzqzqh8kf9ipz2u02 |           2 | 2020-07-12 15:40:22   |
| 20 |         2 | 3p74yrq35nfaazwdo8auguvn2h5hpugtfvvw |           2 | 2020-07-12 16:07:24   |
| 21 |         2 | hpa2am40ufke7o7q2y733hh83h7ykxxdgkof |          16 | 2020-07-12 17:09:35   |
| 22 |         2 | g7adgyzloab2t4z7xx2id0a9cjqx8ojfni99 |           2 | 2020-07-13 12:09:41   |
+----+-----------+--------------------------------------+-------------+-----------------------+

The result will look like below...

+----+-----------+--------------------------------------+-------------+-----------------------+
| id | camera_id |              image_name              | employee_id |      created_at       |
+----+-----------+--------------------------------------+-------------+-----------------------+
| 10 |         2 | pjcc7vf142pec6li7k8kqxuqvnmhm0tyo8ib |          16 | 2020-07-11 10:40:20   |
| 11 |         2 | 9iizfdtk3m81a745ut7tzqzqh8kf9ipz2u02 |           2 | 2020-07-11 10:40:22   |
| 17 |         2 | tapufkiuj5toxfdoikjicbe3k7tl32yj5khp |          16 | 2020-07-12 12:09:47   |
| 19 |         2 | 9iizfdtk3m81a745ut7tzqzqh8kf9ipz2u02 |           2 | 2020-07-12 15:40:22   |
| 22 |         2 | g7adgyzloab2t4z7xx2id0a9cjqx8ojfni99 |           2 | 2020-07-13 12:09:41   |
+----+-----------+--------------------------------------+-------------+-----------------------+

Question has been marked as duplicate but it is different from the mentioned duplicate in sense that there is sub-grouping in each day as well. — Mani G, Jul 13 '20 at 23:21

score 1 · Answer 1 · answered Jul 13 '20 at 22:54

how to get earliest entry in each day by each employee

You can filter with a correlated subquery:

select t.*
from mytable t
where t.created_at = (
    select min(t1.created_at)
    from mytable t1
    where 
        t1.employee_id    =  t.employee_id 
        and t1.created_at >= date(t.created_at)
        and t1.created_at <  date(t.created_at) + interval 1 day
)

This query would take advantage of an index on (employee_id, created_at).

Or, if you are running MySQL 8.0, you can use window functions:

select *
from (
    select 
        t.*,
        row_number() over(
            partition by employee_id, date(created_at) 
            order by created_at
        ) rn
    from mytable t
) t
where rn = 1

score 1 · Accepted Answer · answered Jul 13 '20 at 22:54

1

You can do:

select *
from t
where (employee_id, created_at) in (
  select employee_id, min(created_at)
  from t
  group by employee_id, date(created_at)
)

answered Jul 13 '20 at 22:54

The Impaler

45,731
9
39
76

Select complete record with earliest timestamp on a day for each employee

2 Answers2