开发者

MySQL getting each first unique row from sorted by a date column?

开发者 https://www.devze.com 2023-02-09 11:07 出处:网络
I have a table as below, I\'m having trouble getting the resultsI 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

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
0

精彩评论

暂无评论...
验证码 换一张
取 消