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

Question

J'ai une table ("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'essaye de créer une vue de cette table qui n'afficherait que l'enregistrement le plus récent par identifiant d'utilisateur, tout en me donnant la valeur "in" ou "out", donc quelque chose comme ça :

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

J'en suis assez proche pour l'instant, mais j'ai réalisé que les vues n'acceptent pas les sous-enchères, ce qui rend les choses beaucoup plus difficiles. La requête la plus proche que j'ai obtenue est :

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 je reçois 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 lui, il renvoie le temps le plus récent, mais pas avec sa valeur IO relative.

Des idées ? Merci !

Answer 1

Une requête :

Exemple SQLFIDDLE

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 |

Notez que si un utilisateur a plusieurs enregistrements avec le même temps "maximum", la requête ci-dessus retournera plus d'un enregistrement. Si vous ne voulez qu'un seul enregistrement par utilisateur, utilisez la requête ci-dessous :

Exemple SQLFIDDLE

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)

Answer 2

Il n'est pas nécessaire d'essayer de réinventer la roue, car c'est une pratique courante. le problème du plus grand nombre de personnes par groupe . Très bien. La 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 y a deux enregistrements différents avec la même valeur la plus grande dans le même groupe - grâce à l'astuce de la fonction (t1.time = t2.time AND t1.Id < t2.Id) . Tout ce que je fais ici est de m'assurer que dans le cas où deux enregistrements du même utilisateur ont la même heure, un seul est choisi. Peu importe que le critère soit Id ou quelque chose d'autre - en fait, tout critère qui est garanti comme unique ferait l'affaire ici.

Answer 3

D'après la réponse de @TMS, je l'aime bien parce qu'il n'y a pas besoin de sous-requêtes mais je pense que l'omission de l'élément 'OR' sera suffisante 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 heures nulles, vous pouvez les filtrer dans le champ WHERE clause :

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

Answer 4

Déjà résolu, mais 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

Answer 5

Si vous utilisez MySQL 8.0 ou supérieur, vous pouvez utiliser Fonctions des fenêtres :

Une requête :

DBFiddleExample

SELECT DISTINCT
FIRST_VALUE(ID) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS ID,
FIRST_VALUE(USER) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS USER,
FIRST_VALUE(TIME) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS TIME,
FIRST_VALUE(IO) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS IO
FROM lms_attendance;

Résultat :

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

L'avantage que je vois par rapport à l'utilisation du solution proposée par Justin est qu'il vous permet de sélectionner la ligne contenant les données les plus récentes par utilisateur (ou par identifiant, ou par autre chose), même à partir de sous-requêtes, sans avoir besoin d'une vue ou d'une table intermédiaire.

Et si vous utilisez un HANA, il est également ~7 fois plus rapide :D