Seleccionar fila con la fecha más reciente por usuario
Tengo una tabla ("lms_attendance") de los tiempos de entrada y salida de los usuarios que se ve así:
id user time io (enum)
1 9 1370931202 out
2 9 1370931664 out
3 6 1370932128 out
4 12 1370932128 out
5 12 1370933037 in
Estoy tratando de crear una vista de esta tabla que genere solo el registro más reciente por id de usuario, mientras me da el valor " in " o "out", así que algo como:
id user time io
2 9 1370931664 out
3 6 1370932128 out
5 12 1370933037 in
Estoy bastante cerca hasta ahora, pero me di cuenta de que las vistas no aceptarán subquerys, lo que lo hace mucho más difícil. La consulta más cercana que recibí fue :
select
`lms_attendance`.`id` AS `id`,
`lms_attendance`.`user` AS `user`,
max(`lms_attendance`.`time`) AS `time`,
`lms_attendance`.`io` AS `io`
from `lms_attendance`
group by
`lms_attendance`.`user`,
`lms_attendance`.`io`
Pero lo que consigo es:
id user time io
3 6 1370932128 out
1 9 1370931664 out
5 12 1370933037 in
4 12 1370932128 out
Que es cerca, pero no perfecto. Sé que el último grupo por no debería estar allí, pero sin él, devuelve el tiempo más reciente, pero no con su valor relativo de IO.
¿Alguna idea? ¡Gracias!
8 answers
Consulta:
SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
FROM lms_attendance t2
WHERE t2.user = t1.user)
Resultado:
| ID | USER | TIME | IO |
--------------------------------
| 2 | 9 | 1370931664 | out |
| 3 | 6 | 1370932128 | out |
| 5 | 12 | 1370933037 | in |
Solución que va a funcionar cada vez:
SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
FROM lms_attendance t2
WHERE t2.user = t1.user
ORDER BY t2.id DESC
LIMIT 1)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-06-11 07:50:35
No hay necesidad de intentar reinventar la rueda, ya que esto es común problema mayor-n-por-grupo. Muy buena solución se presenta.
Prefiero la solución más simplista (ver SQLFiddle, actualizado de Justin) sin subconsultas (por lo tanto fácil de usar en vistas):
SELECT t1.*
FROM lms_attendance AS t1
LEFT OUTER JOIN lms_attendance AS t2
ON t1.user = t2.user
AND (t1.time < t2.time
OR (t1.time = t2.time AND t1.Id < t2.Id))
WHERE t2.user IS NULL
Esto también funciona en un caso donde hay dos registros diferentes con el mismo mayor valor dentro del mismo grupo - gracias al truco con (t1.time = t2.time AND t1.Id < t2.Id)
. Todo lo que estoy haciendo aquí es asegurar que en caso en que dos registros del mismo usuario tienen el mismo tiempo solo se elige uno. En realidad no importa si el criterio es Id
o algo más - básicamente cualquier criterio que se garantiza que es único haría el trabajo aquí.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:18:22
Basado en la respuesta @TMS, me gusta porque no hay necesidad de subconsultas, pero creo que ommiting la parte 'OR'
será suficiente y mucho más simple de entender y leer.
SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
ON t1.user = t2.user
AND t1.time < t2.time
WHERE t2.user IS NULL
Si no estás interesado en filas con tiempos nulos puedes filtrarlas en la cláusula WHERE
:
SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
ON t1.user = t2.user
AND t1.time < t2.time
WHERE t2.user IS NULL and t1.time IS NOT NULL
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-03-16 18:35:49
Ya resuelto, pero solo para el registro, otro enfoque sería crear dos vistas...
CREATE TABLE lms_attendance
(id int, user int, time int, io varchar(3));
CREATE VIEW latest_all AS
SELECT la.user, max(la.time) time
FROM lms_attendance la
GROUP BY la.user;
CREATE VIEW latest_io AS
SELECT la.*
FROM lms_attendance la
JOIN latest_all lall
ON lall.user = la.user
AND lall.time = la.time;
INSERT INTO lms_attendance
VALUES
(1, 9, 1370931202, 'out'),
(2, 9, 1370931664, 'out'),
(3, 6, 1370932128, 'out'),
(4, 12, 1370932128, 'out'),
(5, 12, 1370933037, 'in');
SELECT * FROM latest_io;
Haga clic aquí para verlo en acción en SQL Fiddle
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-03-23 09:34:06
Prueba esta consulta:
select id,user, max(time), io
FROM lms_attendance group by user;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-09-22 05:38:04
select b.* from
(select
`lms_attendance`.`user` AS `user`,
max(`lms_attendance`.`time`) AS `time`
from `lms_attendance`
group by
`lms_attendance`.`user`) a
join
(select *
from `lms_attendance` ) b
on a.user = b.user
and a.time = b.time
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-06-11 07:18:18
Posiblemente puede hacer grupo por usuario y luego ordenar por desc tiempo. Algo como lo siguiente
SELECT * FROM lms_attendance group by user order by time desc;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-06-11 07:17:03
Esto funcionó para mí:
SELECT user, time FROM
(
SELECT user, time FROM lms_attendance --where clause
) AS T
WHERE (SELECT COUNT(0) FROM table WHERE user = T.user AND time > T.time) = 0
ORDER BY user ASC, time DESC
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-12-13 21:59:37