Tutorial :MySQL: How can I select only the last update for each user?



Question:

Say I have a 'user_log' table with the following field:

  • id
  • user_id
  • status_text
  • timestamp

How do I write a query that selects only the last update for all the users in that table?

Appreciate your help :)


Solution:1

Assuming ID is an auto-increment id, you can do this:

SELECT *   FROM user_log   INNER JOIN   (SELECT MAX(id) as id FROM user_log GROUP BY user_id) last_updates   ON last_updates.id = user_log.id  

If you want to be really pedantically correct and cover all the corner cases (multiple updates with the same timestamp, out-of-order insertions, etc) you can use the timestamp:

SELECT *   FROM user_log  INNER JOIN  (SELECT MAX(id)   FROM user_log    INNER JOIN    (SELECT DISTINCT user_id, MAX(timestamp) as timestamp     FROM user_log GROUP BY user_id   ) last_updates    ON last_updates.user_id = user_log.user_id       AND last_updates.timestamp = user_log.timestamp  ) last_update  ON last_update.id = user_log.id  


Solution:2

Use:

  SELECT ul.user_id,           MAX(ul.timestamp)      FROM USER_LOG ul  GROUP BY ul.user_id  


Solution:3

A correlated subquery can be used to get the id of the record with the most recent timestamp. This should work even if timestamps are not unique or id's are not given sequentially.

select      ul.id,      ul.user_id,      ul.status_text,       ul.timestamp  from      user_log ul  where       ul.id = (select top 1 ul2.id               from user_log ul2               where ul2.user_id = ul.user_id              order by ul2.timestamp desc)  


Solution:4

If you don't have a unique constraint on (user_id, timestamp) but you still want to guarantee that only one row is returned per user then you can use this query:

SELECT id, user_id, status_text, timestamp  FROM (      SELECT *,  @prev <> user_id AS is_newest, @prev := user_id      FROM user_log, (SELECT @prev := -1) AS vars      ORDER BY user_id, timestamp DESC, id DESC  ) AS T1  WHERE is_newest  

Result:

1, 1, 'Foo', '2010-01-01 00:00'  4, 2, 'Bar', '2010-01-01 01:00'  

Test data:

CREATE TABLE user_log (id INT NOT NULL, user_id INT NOT NULL, status_text NVARCHAR(100) NOT NULL, timestamp NVARCHAR(100) NOT NULL);  INSERT INTO user_log (id, user_id, status_text, timestamp) VALUES  (1, 1, 'Foo', '2010-01-01 00:00'),  (2, 1, 'Bar', '2010-01-01 00:00'),  (3, 2, 'Foo', '2010-01-01 00:00'),  (4, 2, 'Bar', '2010-01-01 01:00');  


Solution:5

Try with:

SELECT user_id, MAX(timestamp) FROM user_log GROUP BY user_id  


Solution:6

Is that a n-n relation? if not, status_text and timestamp should be absorbed by the biggest table in the relation, according to the standard guidelines.

If not try

SELECT id,MAX(timestamp) FROM user_log GROUP BY user_id;  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »