title: Select row with most recent date per user
body: <p>I have a table ("lms_attendance") of users' check-in and out times that looks like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>id  user    time    io
  2   9   1370931664  out
  3   6   1370932128  out
  5   12  1370933037  in
  </code></pre>
  
  <p>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 :</p>
  
  <pre><code>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`
  </code></pre>
  
  <p>But what I get is :</p>
  
  <pre><code>id  user    time    io
  3   6   1370932128  out
  1   9   1370931664  out
  5   12  1370933037  in
  4   12  1370932128  out
  </code></pre>
  
  <p>Which is close, but not perfect. I know that last group by shouldn't be there, but without it, it returns the most recent time, but not with it's relative IO value.</p>
  
  <p>Any ideas?
  Thanks!</p>