MySQL getting each first unique row from sorted by a date column?
I have a table as below, I'm having trouble getting the results I want.
What I want is to get just the recent message of every unique user_id
. I have asked a similar question before, but I feel like an idiot because I still does not get it. How am I suppose to use MySQL order by
and group by
together? Using distinct
doesn't work either.
+---------+---------------------+------------------+
| user_id | cre开发者_如何转开发ated_at | message |
+---------+---------------------+------------------+
| 2 | 2011-02-06 19:53:59 | sd |
| 2 | 2011-02-06 20:11:41 | working on st.. |
| 3 | 2011-02-06 20:40:14 | testing applica..|
| 3 | 2011-02-06 21:35:11 | testing appli.. |
| 3 | 2011-02-06 23:09:34 | testing af.. |
+---------+---------------------+------------------+
This is how it should return
+---------+---------------------+------------------+
| user_id | created_at | message |
+---------+---------------------+------------------+
| 2 | 2011-02-06 20:11:41 | working on st.. |
| 3 | 2011-02-06 23:09:34 | testing af.. |
+---------+---------------------+------------------+
This does return in right format, but it fetches some random message.
select user_id,created_at, message from status group by user_id order by created_at desc
Thank you.
SELECT
user_id,
created_at,
message
FROM
status
JOIN
(SELECT user_id,MAX(created_at) AS max
FROM status
GROUP BY user_id) max_created_at ON
(max_created_at.user_id = user_id AND max_created_at.max = created_at)
select distinct(user_id) from status order by created_at DESC
精彩评论