Razvan T. Razvan T. - 6 months ago 8
MySQL Question

How can I sort descending the result of a Group by?

I have a table

messages
with the following fields:

id
,
surname
,
name
,
message
,
message_date


I am trying to write the correct query so as to display all the rows grouped by
surname
and sorted by date in descending order (I want the group to display only the most recent record, based on
message_date
).

For example, if I have the following records:

- 1, John Doe, my message, 2016-04-24 11:13:24
- 2, Johnny Doe, hello, 2016-05-22 15:23:05
- 3, John Doe, another message, 2016-05-23 16:10:07


I would like to obtain the following result:

- 3, John Doe, another message, 2016-05-23 16:10:07
- 2, Johnny Doe, hello, 2016-05-22 15:23:05


I tried to use the following query but it doesn't work properly:

$sql="SELECT *
FROM messages
WHERE (id, surname, name, message, message_date) IN (
SELECT id, surname, name, message, MAX(message_date)
FROM messages
GROUP BY surname)";


Thank you!

Answer

You problem is that you are also using message for the IN() statement, which is different for between each message . Discard it and this should work:

$sql="SELECT *
      FROM   messages
      WHERE  (surname, name, message_date) IN (SELECT surname, name, MAX(message_date)
                                                   FROM messages
                                                   GROUP BY surname, name)
      ORDER BY message_date DESC";
Comments