S
S
Steely2015-03-05 13:08:26
MySQL
Steely, 2015-03-05 13:08:26

How to select all users with latest status (two mysql tables)?

There are two tables:
Users (id, etc.)
User statuses (userid, status, datetime) It is
required to select all users with the latest status by time. How to do it in one request?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
S
Steely, 2015-03-05
@Steely

While I came up with this, is there another option?
SELECT *
FROM users
JOIN status ON status.userid=users.id
WHERE status.datetime = (SELECT MAX(datetime) FROM status WHERE users.id = status.userid)

I
Igor, 2015-03-05
@hurgadan

select u.*, a.status, a.last_time
from 
    users u, 
    (select `userid`, `status`, max(`datetime`) as last_time from `status` group by `userid`, `status` ) as a
where a.user_id = u.user_id

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question