How to select last message for user in mysql database?

Question

I have the following table setting:

posts table name

 Id, user_id, title, content, date 

Now I need to select the last (last) message of each user from the table above Can anyone help, how can we do this?

right now i'm using

 SELECT * FROM `posts` GROUP BY `user_id` ORDER BY `ID` asc 

but actually it just retrieves the first message of each user, but I need the last message of each user.

Answer 1

okey. It may be your problem to do one thing.

  SELECT * FROM (SELECT * FROM posts ORDER BY date desc) AS `result` GROUP BY `result`.`user_id` 

Since the user_id group is used earlier in your example, it will only accept the first identifier; make sure this works!

Answer 2

Estimated posts are appointed in ascending order:

 SELECT max(post_id) AS post_id FROM posts GROUP BY user_id; 

If such an assumption cannot be made, you can do something like:

 SELECT user_id, max(date) AS most_recent FROM posts GROUP BY user_id 

you can use this to capture the full post:

 SELECT p1.* FROM (SELECT max(post_id) AS post_id FROM posts GROUP BY user_id) p2 JOIN p1 ON (p1.post_id = p2.post_id); 

or for the absence of an assumption:

 SELECT p1.* FROM (SELECT user_id, max(date) AS most_recent FROM posts GROUP BY user_id) p2 JOIN p1 ON (p1.user_id = p2.user_id AND p2.most_recent = p1.date); 

You will receive several messages from the user if the latest messages have the same date. Perhaps this is correct, since they are not newer.

Note. They can also be written as correlated subqueries. Benchmarking will tell you which ones are best suited for your particular case.

Answer 3

 SELECT p.* FROM ( SELECT DISTINCT user_id FROM posts ) pd JOIN posts p ON p.id = ( SELECT id FROM posts pi WHERE pi.user_id = pd.user_id ORDER BY user_id DESC, date DESC, id DEST LIMIT 1 ) 

Create an index on posts (user_id, date, id) so that it works quickly.

See examples 4 and 3 here:

• 10 things in MySQL (which will not work properly)

which explain why your original request will not work.

Answer 4

Well, this is ugly, but it works.

MySQL has a convenient fancy group column mode. In other DBMSs, such as MSSQL, if you use GROUP BY , each column in SELECT must be grouped or must be an aggregation function.

In fact, MySQL creates the first record used for grouping. So if you do this:

 SELECT * FROM posts GROUP BY user_id ORDER BY ID desc 

Must work. But if it is not, it means that it first groups and ignores the order, so you can try something like this:

 SELECT * FROM (SELECT * FROM posts WHERE 1 = 1 ORDER BY ID desc) GROUP BY user_id 

It is important that you include filters in the subquery in order to maintain performance at acceptable levels.

Good luck