Select row with most recent date per user

Question

I have a table ("lms_attendance") of users' check-in and out times that 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'm trying to create a view of this table that would output only the most recent record per user id, while giving me the "in" or "out" value, so something like:

id  user    time    io

2   9   1370931664  out

3   6   1370932128  out

5   12  1370933037  in

I'm pretty close so far, but I realized that views won't accept subquerys, which is making it a lot harder. The closest query I got was :

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 what I get is :

id  user    time    io

3   6   1370932128  out

1   9   1370931664  out

5   12  1370933037  in

4   12  1370932128  out

Which is close, but not perfect. I know that the last group by shouldn't be there, but without it, it returns the most recent time, but not with it's relative IO value.

Any ideas? Thanks!

Answer 1

Try using the below query:

You can refer to SQLFIDDLEExample

SELECT t1.*

FROM lms_attendance t1

WHERE t1.time = (SELECT MAX(t2.time)

                 FROM lms_attendance t2

                 WHERE t2.user = t1.user)

Output is as follows:

| ID | USER |       TIME |  IO |

--------------------------------

|  2 |    9 | 1370931664 | out |

|  3 |    6 | 1370932128 | out |

|  5 |   12 | 1370933037 |  in |

Solution which is going to work everytime:

To execute the code you can refer to 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)