Sélectionnez la ligne avec la date la plus récente par utilisateur

J’ai un tableau (“lms_attendance”) des heures d’arrivée et de départ des utilisateurs qui ressemble à ceci:

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 

J’essaie de créer une vue de cette table qui ne produirait que l’enregistrement le plus récent par ID utilisateur, tout en me donnant la valeur “in” ou “out”, donc quelque chose comme:

 id user time io 2 9 1370931664 out 3 6 1370932128 out 5 12 1370933037 in 

Je suis assez proche jusqu’ici, mais j’ai réalisé que les vues n’accepteront pas les sous-requêtes, ce qui rend la tâche beaucoup plus difficile. La requête la plus proche que j’ai reçue était:

 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` 

Mais ce que j’obtiens c’est:

 id user time io 3 6 1370932128 out 1 9 1370931664 out 5 12 1370933037 in 4 12 1370932128 out 

Ce qui est proche, mais pas parfait. Je sais que le dernier groupe par ne devrait pas être là, mais sans cela, il renvoie l’heure la plus récente, mais pas avec sa valeur relative d’E / S.

Des idées? Merci!

Question:

SQLFIDDLEExemple

 SELECT t1.* FROM lms_attendance t1 WHERE t1.time = (SELECT MAX(t2.time) FROM lms_attendance t2 WHERE t2.user = t1.user) 

Résultat:

 | ID | USER | TIME | IO | -------------------------------- | 2 | 9 | 1370931664 | out | | 3 | 6 | 1370932128 | out | | 5 | 12 | 1370933037 | in | 

Solution qui fonctionnera à chaque fois:

SQLFIDDLEExemple

 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) 

Pas besoin d’essayer de réinventer la roue, car c’est le plus grand problème de n-par-groupe . Très belle solution est présentée .

Je préfère la solution la plus simpliste ( voir SQLFiddle, mise à jour de Justin ) sans sous-requêtes (donc facile à utiliser dans les vues):

 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 

Cela fonctionne également dans le cas où il existe deux enregistrements différents avec la même plus grande valeur dans le même groupe - grâce à l'astuce avec (t1.time = t2.time AND t1.Id < t2.Id) . Tout ce que je fais ici est d’assurer que si deux enregistrements du même utilisateur ont le même temps, un seul est choisi. Peu importe si les critères sont Id ou autre chose - fondamentalement, tout critère garanti pour être unique rendrait le travail ici.

Basé sur la réponse de @TMS, je l’aime bien parce qu’il n’y a pas besoin de sous-requêtes, mais je pense qu’inclure la partie 'OR' sera suffisant et beaucoup plus simple à comprendre et à lire.

 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 vous n'êtes pas intéressé par les lignes avec des temps nuls, vous pouvez les filtrer dans la clause 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 

Déjà résolu, mais juste pour mémoire, une autre approche serait de créer deux vues …

 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; 

Cliquez ici pour le voir en action à SQL Fiddle

Essayez cette requête:

  select id,user, max(time), io FROM lms_attendance group by user; 
 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 

Peut-être que vous pouvez faire grouper par utilisateur et ensuite ordonner par heure desc. Quelque chose comme ci-dessous

  SELECT * FROM lms_attendance group by user order by time desc; 

Cela a fonctionné pour moi:

 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