Get latest entry using GROUP BY

I'm pulling out some conversations from my database. They are being grouped by the user_from column.

As of now, it outputs the oldest message. I want it to show the newest message.

What is the most simple way of doing 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

mail table

user table (snippet)

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

---

MySQL is unfortunately very lenient about the contents of the GROUP BY clause, which produces unreliable results unless you include all columns in the GROUP BY . It is never recommended to SELECT * in a join query, but we'll leave that for now. What you need to do is perform a subquery join which gets the most recent message for the user by date, joined against 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 show all most recent senders rather than only one.

链接地址: http://www.djcxy.com/p/68640.html

上一篇: isKindOfClass行为

下一篇: 使用GROUP BY获取最新条目