ORDER BY datetime on GROUP_CONCAT not working

Question

I have created a table in MySQL with following columns:

id - from_id - to_id - datetime - message

In the the datetime column the datetime is stored like this:

2016-07-28 17:36:24
2016-07-28 17:39:24
2016-07-28 17:41:15

I amusing GROUP_CONCAT on to_id to store all messages from from_id and display only 1 message (the latest). My query is:

SELECT id, from_id, time_sent, message, GROUP_CONCAT(to_id order BY 
time_sent DESC) FROM messages WHERE to_id = '1' GROUP BY from_id

I get all the results hroup by from_id but the message is the very first one. Seems like the order BY time_sent DESC is not been applied.

I have tried order BY UNIX_TIMESTAMP(time_sent) DESC, tried putting order by at the end of the query. Nothing worked. I want the latest message to be displayed by from_id.

`SELECT id, from_id, time_sent, message ... GROUP BY from_id` As you see you group by `from_id` but display more columns without aggregation function. Now MySQL could choose any row. Related: [Group by clause in mySQL and postgreSQL, why the error in postgreSQL?](http://stackoverflow.com/a/33629201/5070879) — Lukasz Szozda, Jul 30 '16 at 08:11
Can you give **sql fiddle** with your data so We can give answer fast as much as possible — Sadikhasan, Jul 30 '16 at 08:21
I think your query have a bug because in where clause you write "to_id = '1' " and GROUP_CONCAT on to_id then the result will be just "1" — Mostafa Vatanpour, Jul 30 '16 at 08:57
there are multiple results .. but i want to see the latest message from_id, There are multiple `from_id` with multiple rows which has a message to `to_id` so it groups ok but latest message is not showing. — Somename, Jul 30 '16 at 09:07
I agree. GROUP_CONCAT is just to return a string of 1's. What's the point of that? — Strawberry, Jul 30 '16 at 09:15
@Sadikhasan https://docs.google.com/document/d/1czwPz9uVBn3iKPDVXCcbdBMhLuIupNcNuGT3yU4wCPo/edit?usp=sharing — Somename, Jul 30 '16 at 09:23
@Strawberry The string of 1's is the list of all the `from_id` who sent message to `to_id` 1. Is that what you mean ? — Somename, Jul 30 '16 at 09:24
`The string of 1's is the list of all the from_id` Er, no it isn't — Strawberry, Jul 30 '16 at 11:18
If you like, consider following this simple two-step course of action:1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Jul 30 '16 at 11:19
@Somename Check http://stackoverflow.com/a/38673478/2893413 May be useful to you — Sadikhasan, Jul 30 '16 at 11:43

Sadikhasan · Answer 1 · 2016-07-30T11:48:45.557

0

SELECT id,
       from_id,
       time_sent,
       message,
       GROUP_CONCAT(to_id)
FROM
  (SELECT *
   FROM messages
   WHERE to_id = '1'
   ORDER BY time_sent DESC) AS test
GROUP BY from_id

SQL Fiddle

edited Jul 30 '16 at 11:48

answered Jul 30 '16 at 11:33

Sadikhasan

18,365
21
80
122

Sadik bro .. Now this is more frustrating. Its working on the SQL Fiddle, I can see that, but its not on MySQL on my local. When i try it with php or even in the Console of phpadmin for mysql, i am getting only the very first post and the from_id is in ASC. Please check http://imgur.com/a/BoFRf .. What am i doing wrong? – Somename Jul 30 '16 at 12:22

ORDER BY datetime on GROUP_CONCAT not working

1 Answers1