Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

enter image description here

user table (snippet)

enter image description here

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

like image 524
Patrick Reck Avatar asked Jan 23 '13 13:01

Patrick Reck


People also ask

How do you find the latest record by GROUP BY?

Let's say you want to get last record in each group, that is, for each product. First we use GROUP BY to get most recent date for each group. Now that we know the most recent date for each group, we join this result with our original table to get latest record by date group.

How do I get most recent entry in SQL?

Use the aggregate MAX(signin) grouped by id. This will list the most recent signin for each id . To get the whole single record, perform an INNER JOIN against a subquery which returns only the MAX(signin) per id.

Can I use WHERE by clause after GROUP BY?

GROUP BY Clause is utilized with the SELECT statement. GROUP BY aggregates the results on the basis of selected column: COUNT, MAX, MIN, SUM, AVG, etc. GROUP BY returns only one result per group of data. GROUP BY Clause always follows the WHERE Clause.

Can we use ORDER BY with GROUP BY?

Using Group By and Order By Together When combining the Group By and Order By clauses, it is important to bear in mind that, in terms of placement within a SELECT statement: The GROUP BY clause is placed after the WHERE clause. The GROUP BY clause is placed before the ORDER BY clause.


1 Answers

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.

like image 163
Michael Berkowski Avatar answered Oct 01 '22 10:10

Michael Berkowski