How to get first and last record of each user in MySQL (for attendance)

Question

I have the following table:

CREATE TABLE IF NOT EXISTS `access_log` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL DEFAULT 0,
    `room_id` INT(11) NOT NULL DEFAULT 0,
    `created` TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

A new record is added everytime a user enters a room room_id. I would like to select the first and last record of every user for every room.

Currently I have the following queries that don't seem to give the right records:

For first record of each room for each user:

SELECT al.* FROM `access_log` AS `al`
LEFT JOIN `rooms` AS `r` ON al.room_id = r.id
INNER JOIN (
    SELECT user_id, room_id, min(created) AS min_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.min_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59'

For last record of each room for each user:

SELECT al.* FROM `access_log` AS `al`
LEFT JOIN `rooms` AS `r` ON al.room_id = r.id
INNER JOIN (
    SELECT user_id, room_id, max(created) AS max_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.max_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59'

Here's an SQLFiddle demo including sample data http://www.sqlfiddle.com/#!9/fc5f8b/2. You can see that the query display unintended results. They do not list the different days, although they list the different rooms. Also, the number of rows for the first and last queries are different.

DDLs of same:

CREATE TABLE IF NOT EXISTS `access_log` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL DEFAULT 0,
    `room_id` INT(11) NOT NULL DEFAULT 0,
    `created` TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `access_log` (`id`, `user_id`, `room_id`, `created`) VALUES
(1, 90000017, 6, '2019-06-10 01:15:00'),
(2, 90000017, 6, '2019-06-10 01:25:00'),
(3, 90000018, 6, '2019-06-10 02:15:00'),
(4, 90000018, 6, '2019-06-10 02:25:00'),
(5, 90000019, 6, '2019-06-10 03:15:00'),
(6, 90000019, 6, '2019-06-10 03:25:00'),

(7, 90000017, 5, '2019-06-10 11:15:00'),
(8, 90000017, 5, '2019-06-10 11:25:00'),
(9, 90000018, 5, '2019-06-10 12:15:00'),
(10, 90000018, 5, '2019-06-10 12:25:00'),
(11, 90000019, 5, '2019-06-10 13:15:00'),
(12, 90000019, 5, '2019-06-10 13:25:00'),

(13, 90000017, 6, '2019-06-11 04:10:00'),
(14, 90000017, 6, '2019-06-11 04:20:00'),
(15, 90000018, 6, '2019-06-11 05:10:00'),
(16, 90000018, 6, '2019-06-11 05:20:00'),
(17, 90000019, 6, '2019-06-11 06:10:00'),
(18, 90000019, 6, '2019-06-11 06:20:00'),

(19, 90000017, 5, '2019-06-11 14:10:00'),
(20, 90000017, 5, '2019-06-11 14:20:00'),
(21, 90000018, 5, '2019-06-11 15:10:00'),
(22, 90000018, 5, '2019-06-11 15:20:00'),
(23, 90000019, 5, '2019-06-11 16:20:00'),
(24, 90000019, 5, '2019-06-11 16:20:00');

The expected results should be something like:

First per user per room per day
+------+-----------+---------+---------------------+
|  id  |  user_id  | room_id |       created       |
+------+-----------+---------+---------------------+
|  1   | 90000017  |    6    | 2019-06-10 01:15:00 |
|  3   | 90000018  |    6    | 2019-06-10 02:15:00 |
|  5   | 90000019  |    6    | 2019-06-10 03:15:00 |
|  7   | 90000017  |    5    | 2019-06-10 11:15:00 |
|  9   | 90000018  |    5    | 2019-06-10 12:15:00 |
|  11  | 90000019  |    5    | 2019-06-10 13:15:00 |
|  13  | 90000017  |    6    | 2019-06-11 04:10:00 |
|  15  | 90000018  |    6    | 2019-06-11 05:10:00 |
|  17  | 90000019  |    6    | 2019-06-11 06:10:00 |
|  19  | 90000017  |    5    | 2019-06-11 14:10:00 |
|  21  | 90000018  |    5    | 2019-06-11 15:10:00 |
|  23  | 90000019  |    5    | 2019-06-11 16:20:00 |
+------+-----------+---------+---------------------+

Last per user per room per day
+------+-----------+---------+---------------------+
|  id  |  user_id  | room_id |       created       |
+------+-----------+---------+---------------------+
|  2   | 90000017  |    6    | 2019-06-10 01:25:00 |
|  4   | 90000018  |    6    | 2019-06-10 02:25:00 |
|  6   | 90000019  |    6    | 2019-06-10 03:25:00 |
|  8   | 90000017  |    5    | 2019-06-10 11:25:00 |
|  10  | 90000018  |    5    | 2019-06-10 12:25:00 |
|  12  | 90000019  |    5    | 2019-06-10 13:25:00 |
|  14  | 90000017  |    6    | 2019-06-11 04:20:00 |
|  16  | 90000018  |    6    | 2019-06-11 05:20:00 |
|  18  | 90000019  |    6    | 2019-06-11 06:20:00 |
|  20  | 90000017  |    5    | 2019-06-11 14:20:00 |
|  22  | 90000018  |    5    | 2019-06-11 15:20:00 |
|  24  | 90000019  |    5    | 2019-06-11 16:20:00 |
+------+-----------+---------+---------------------+

Write a query to get the first, another query to get the last, and then combine them with `UNION`. — Barmar, Jun 11 '19 at 20:38
I've already done that but it didn't work, that's why I posted the question. — Mike Feng, Jun 11 '19 at 21:01
Why are you joining with `pacc_leapaccess_rooms`? You never use anything from that table. And the subquery should use `access_log`, not `pacc_leapaccess_access_log`. — Barmar, Jun 11 '19 at 21:15
I've added a inspection query in your fiddle, can you please check if the result return anything close to your expected output? http://www.sqlfiddle.com/#!9/fc5f8b/7 thanks — FanoFN, Jun 12 '19 at 01:32
@tcadidot0 the correct result is to show the first/last entry of each user for each room for each day — Mike Feng, Jun 12 '19 at 03:35
@tcadidot0 your query does not show the different days for min and max, they're all from the same day. — Mike Feng, Jun 12 '19 at 03:37
@tcadidot0 yes exactly! aha I just edited my question to include the expected results. Can you explain your query? My actual query includes a lot of other joins, and there are other fields in the `access_log` table where conditions are applied. I'm not sure how I can include your query to the rest of mine. — Mike Feng, Jun 12 '19 at 03:59

score 1 · Accepted Answer · answered Jun 12 '19 at 04:11

I've suggest with this single query to cross check with the expected result:

SELECT 
   GROUP_CONCAT(id ORDER BY created,id SEPARATOR ' ') all_id, 
   -- this return all id present in the group
   SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY created,id SEPARATOR ' '),' ',1) min_id_in,
   -- this part is taking the first value from the GROUP_CONCAT operation above
   SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY created,id SEPARATOR ' '),' ',-1) max_id_in,
   -- this part is taking the last value from the first GROUP_CONCAT operation
   user_id,room_id,
   MIN(created), 
   MAX(created) -- Min/max value are both shown in same query
FROM access_log 
GROUP BY user_id,room_id,
date(created); -- the missing condition where OP's asks results to return by each date.

I've added date(created) in the GROUP BY .. condition.

In your original query from the fiddle:

SELECT al.* FROM `access_log` AS `al`
INNER JOIN (
    SELECT user_id, room_id, min(created) AS min_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id, 
    date(created) -- I've added the condition here 
    ) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.min_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59'
ORDER BY al.user_id ASC;


SELECT al.* FROM `access_log` AS `al`
INNER JOIN (
    SELECT user_id, room_id, max(created) AS max_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id, 
    date(created) -- and here
    ) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.max_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59'
ORDER BY al.user_id ASC;

Worked beautifully, thank you. For anyone else looking for a similar solution, please read the comment thread of the question as well. — Mike Feng, Jun 12 '19 at 04:23

score 0 · Answer 2 · answered Jun 11 '19 at 21:19

The subquery should select from the same table as the main query, so it should select from access_log, not pacc_leapaccess_access_log.

SELECT al.* FROM `access_log` AS `al`
LEFT JOIN `pacc_leapaccess_rooms` AS `r` ON al.room_id = r.id
INNER JOIN (
    SELECT user_id, room_id, min(created) AS min_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.min_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59';

SELECT al.* FROM `access_log` AS `al`
LEFT JOIN `pacc_leapaccess_rooms` AS `r` ON al.room_id = r.id
INNER JOIN (
    SELECT user_id, room_id, max(created) AS max_date
    FROM `access_log`
    WHERE `user_id` != 0
    GROUP BY user_id, room_id) AS al2
ON al.user_id = al2.user_id AND al.room_id = al2.room_id AND al.created = al2.max_date
WHERE `al`.`created` >= '2019-06-09 00:00:00' AND `al`.`created` <= '2019-06-12 23:59:59';

I made a typo and have fixed it. My code is exactly as the one you provided. I have added an sqlfiddle here http://www.sqlfiddle.com/#!9/fc5f8b/2 — Mike Feng, Jun 11 '19 at 22:47

How to get first and last record of each user in MySQL (for attendance)

2 Answers2