Get the latest record using GROUP BY

Question

I get a few conversations from my database. They are grouped by the user_from column.

He is currently issuing the oldest message. I want it to display the latest message.

What is the easiest way to do this?

SELECT * FROM (`mail`) JOIN `users` ON `users`.`id` = `mail`.`user_from` JOIN `users_info` ON `users_info`.`user_id` = `mail`.`user_from` WHERE `user_to` = '1' GROUP BY `user_from` ORDER BY `mail`.`date` desc 

post desk

user table (fragment)

This is the current working code. SecretAgent sent a message newer than Mail from the agency, which it should display instead

Answer 1

MySQL, unfortunately, is very mild about the contents of the GROUP BY , which leads to unreliable results if you do not include all the columns in GROUP BY . This is never recommended by SELECT * in a connection request, but we will leave it for now. What you need to do is run a subquery that receives the last message for the user by date, combined with the rest of the columns.

 SELECT /* Don't actually do this. Be explicit about columns and assign aliases where their names collide */ users.*, users_info.*, mail.* FROM `users` JOIN `mail` ON `users`.`id` = `mail`.`user_from` JOIN `users_info` ON `users_info`.`user_id` = `mail`.`user_from` /* Joined subquery gets most recent message from each user */ JOIN ( SELECT user_from, MAX(date) AS date FROM mail WHERE user_to = '1' GROUP BY user_from /* Joined back to the main mail table on user_from and date */ ) most_recent ON mail.user_from = most_recent.user_from AND mail.date = most_recent.date WHERE `user_to` =  '1' 

Edit Updated to display all recent senders, not just one.