Seleziona la riga con la data più recente per utente

Ho una tabella (“lms_attendance”) dei tempi di check in e out degli utenti che assomiglia a questo:

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 

Sto cercando di creare una vista di questa tabella che restituisca solo il record più recente per ID utente, mentre mi dà il valore “in” o “out”, quindi qualcosa come:

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

Sono abbastanza vicino finora, ma mi sono reso conto che le visualizzazioni non accetteranno i sottotitoli, il che lo rende molto più difficile. La domanda più vicina che ho ottenuto è stata:

 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` 

Ma quello che ottengo è:

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

Che è vicino, ma non perfetto. So che l’ultimo gruppo di non dovrebbe essere lì, ma senza di esso, restituisce il tempo più recente, ma non con il suo valore di I / O relativo.

Qualche idea? Grazie!

Query:

SQLFIDDLEExample

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

Risultato:

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

Soluzione che funziona ogni volta:

SQLFIDDLEExample

 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) 

Non c’è bisogno di provare a reinventare la ruota, dato che questo è un problema comune più grande per ogni gruppo . È presentata una soluzione molto bella.

Preferisco la soluzione più semplice ( vedi SQLFiddle, Justin aggiornato ) senza sottoquery (quindi facile da usare nelle viste):

 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 

Ciò funziona anche nel caso in cui vi siano due record diversi con lo stesso valore maggiore all'interno dello stesso gruppo, grazie al trucco con (t1.time = t2.time AND t1.Id < t2.Id) . Tutto quello che sto facendo qui è assicurare che, nel caso in cui due record dello stesso utente abbiano lo stesso tempo, viene scelto solo uno. In realtà non importa se il criterio è Id o qualcos'altro - in pratica qualsiasi criterio che è garantito essere unico potrebbe fare il lavoro qui.

Basato sulla risposta @TMS, mi piace perché non c’è bisogno di sottoquery, ma penso che l’intervento sulla parte 'OR' sia sufficiente e molto più semplice da comprendere e leggere.

 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 

se non sei interessato a righe con tempi nulli puoi filtrarle nella clausola 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 

Già risolto, ma solo per la cronaca, un altro approccio sarebbe quello di creare due viste …

 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; 

Clicca qui per vederlo in azione su SQL Fiddle

Prova questa query:

  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 

Probabilmente puoi fare gruppo per utente e poi ordinare per volta desc. Qualcosa come di seguito

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

Questo ha funzionato per me:

 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