Select the row with the most recent date for each user.

Question

I have a table ("lms_attendance") of user registration time and time, which looks like this:

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 

I am trying to create a view of this table that will only output the most recent record to the user ID, giving me a value of "in" or "out", so something like:

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

I'm still pretty close so far, but I realized that submissions will not accept subqueries, which makes it a lot more complicated. Nearest query:

 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` 

But I get:

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

It is close, but not perfect. I know that the last group should not be there, but without it it returns the most recent time, but not with a relative value of IO.

Any ideas? Thank!

Answer 1

Query:

SQLFIDDLEExample

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

Result:

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

A solution that will work every time:

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) 

Answer 2

No need to try to reinvent the wheel, as this is a common largest-n-per-group problem. A very pleasant solution is presented .

I prefer the most simplified solution ( see SQLFiddle updated by Justin's ) without subqueries (thus, easy to use in views):

 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 

This also works when in the same group there are two different records with the same highest value - thanks to the trick with (t1.time = t2.time AND t1.Id < t2.Id) . All I'm doing here is to make sure that in the case when two entries of the same user have the same time, only one is selected. It doesn’t really matter if the Id criteria or something else - in principle, any criteria that are guaranteed to be unique will do the job here.

Answer 3

Based on @TMS's answer, I like it because there is no need for subqueries, but I think that ommiting the 'OR' will be sufficient and much easier to understand and read.

 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 

if you are not interested in strings with zero time, you can filter them in the 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 

Answer 4

Already solved, but just for the record, another approach would be to create two views ...

 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; 

Click here to see it in action on SQL Fiddle

Answer 5

Try this query:

  select id,user, max(time), io FROM lms_attendance group by user; 

Answer 6

 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 

Answer 7

  select result from ( select vorsteuerid as result, count(*) as anzahl from kreditorenrechnung where kundeid = 7148 group by vorsteuerid ) a order by anzahl desc limit 0,1 

Answer 8

Well, it can either be hacking or error prone, but somehow it works as well-

 SELECT id, MAX(user) as user, MAX(time) as time, MAX(io) as io FROM lms_attendance GROUP BY id; 

Answer 9

Perhaps you can make a group by user, and then sort by desc. Something like below

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

Answer 10

This worked for 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