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 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

User · Answer

If your on MySQL 8 0 or higher you can use Window functions   Query   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    Result     ID   USER         TIME    IO   --------------------------------    2      9   1370931664   out      3      6   1370932128   out      5     12   1370933037    in     The advantage I see over using the solution proposed by Justin is that it enables you to select the row with the most recent data per user  or per id  or per whatever  even from subqueries without the need for an intermediate view or table   And in case your running a HANA it is also  7 times faster  D

User · Answer

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     Solution which gonna work everytime   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

User · Answer

Based in  TMS answer  I like it because there s no need for subqueries but I think ommiting the  OR  part will be sufficient and much simpler 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  lt  t2 time WHERE t2 user IS NULL   if you are not interested in rows with null times you can filter them in the WHERE clause   SELECT t1   FROM lms attendance AS t1 LEFT JOIN lms attendance AS t2   ON t1 user   t2 user          AND t1 time  lt  t2 time WHERE t2 user IS NULL and t1 time IS NOT NULL

User · Answer

I have done same thing like below SELECT t1   FROM lms attendance t1 WHERE t1 id in  SELECT max t2 id  as id FROM lms attendance t2 group BY t2 user  This will also reduce memory utilization  Thanks

User · Answer

No need to trying reinvent the wheel  as this is common greatest-n-per-group problem  Very nice solution is presented   I prefer the most simplistic solution  see SQLFiddle  updated 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  lt  t2 time           OR  t1 time   t2 time AND t1 Id  lt  t2 Id   WHERE t2 user IS NULL   This also works in a case where there are two different records with the same greatest value within the same group - thanks to the trick with  t1 time   t2 time AND t1 Id  lt  t2 Id   All I am doing here is to assure that in case when two records of the same user have same time only one is chosen  Doesn t actually matter if the criteria is Id or something else - basically any criteria that is guaranteed to be unique would make the job here

User · Answer

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

User · Answer

Possibly you can do group by user and then order by time desc  Something like as below    SELECT   FROM lms attendance group by user order by time desc

User · Answer

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 at SQL Fiddle

User · Answer

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

User · Answer

Ok  this might be either a hack or error-prone  but somehow this is working as well-  SELECT id  MAX user  as user  MAX time  as time  MAX io  as io FROM lms attendance GROUP BY id

User · Answer

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  gt  T time    0 ORDER BY user ASC  time DESC

User · Answer

Try this query      select id user  max time   io    FROM lms attendance group by user

[mysql] Select row with most recent date per user

Examples related to mysql

Examples related to sql

Examples related to greatest-n-per-group