Home / Questions / 371377
Accepted
Antonio Ruiz
Asked: 2020-07-06 23:48:33 +0800 CST 2020-07-06 23:48:33 +0800 CST

How to get last record per user in MYSQL?

  • 772

I have a table with records of entries and exits by user, I need to obtain the last signing of each user.

This is my query, but it returns repeated USER_IDs and I only want the latest most recent.

SELECT c.ID, c.ID_USUARIO, c.FECHA, c.TIPO
FROM acceso c
INNER JOIN 
(
  SELECT MAX(FECHA) max_time
  FROM acceso
  GROUP BY Date(`FECHA`)
) AS t
ON c.FECHA = t.max_time

My 'access' table:

ID   ID_USUARIO   FECHA                  TIPO
--   ----------   -------------------    ------
1    010          2020-07-05 15:20:01    ENTRADA
2    010          2020-07-05 18:25:00    SALIDA
3    066          2020-07-05 10:00:00    SALIDA
4    066          2020-07-05 12:00:00    ENTRADA
5    066          2020-07-05 18:00:00    SALIDA
6    011          2020-07-05 13:20:01    ENTRADA
7    011          2020-07-06 10:25:00    SALIDA
7    011          2020-07-06 11:25:00    ENTRADA

I want to get:

ID   ID_USUARIO   FECHA                  TIPO
--   ----------   -------------------    ------
2    010          2020-07-05 18:25:00    SALIDA
5    066          2020-07-05 18:00:00    SALIDA
7    011          2020-07-06 11:25:00    ENTRADA

SQLFiddle: http://sqlfiddle.com/#!9/414960/2

mysql

2 Answers

  1. Best Answer

    To get the largest of the dates for each user you have to group by id_usuario. The following would remain:

    SELECT ID_USUARIO, MAX(FECHA) max_time
  FROM acceso
  GROUP BY ID_USUARIO

    What you do with this query is to group by the IDs of the users in the access table and select the largest of the dates, which is equivalent to the last.

    Now, to avoid having the same date for two users, establish a second condition in the JOIN:

    SELECT c.ID, c.ID_USUARIO, c.FECHA, c.TIPO
FROM acceso c
INNER JOIN 
(
  SELECT ID_USUARIO, MAX(FECHA) max_time
  FROM acceso
  GROUP BY ID_USUARIO
) AS t
ON c.FECHA = t.max_time
AND c.ID_USUARIO=t.ID_USUARIO
    • 3

  2. If you only want to show the last records per user when it is "OUT", you must execute this query:

    SELECT *,
       max(fecha) max_fecha
FROM acceso
GROUP BY id_usuario

    In this way you group by id_usuario so that they are not repeated and you filter the last ones from OUTPUT.

    • 0