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

Say I have a 'u开发者_StackOverflow中文版ser_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 :)

---

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

---

Use:

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

---

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)

---

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');

---

Try with:

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

---

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;