Getting an error when I try to run this query in sequel pro [duplicate]

Question

I am trying to build a query that returns the most recent message from two joined tables. But I am getting an error on group by. Maybe I am approaching it the wrong completely.

I know that in my current implementation I will get a list, and not the most recent, but I at least want to see the most recent at the beginning

SELECT m.message, m.created_at
FROM conversations c
JOIN messages m ON c.id = m.conversation_id
WHERE m.conversation_id = 5
GROUP BY m.created_at DESC;

Here's the error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tawkifydb.dm.message' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Answer 1

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tawkifydb.dm.message' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Every column in the SELECT should be in the GROUP BY (or use an aggregate). The error tells that m.message in the SELECT, but not in the GROUP by (and it's not an aggegrate)

You need something like this:

GROUP BY m.created_at, m.message
ORDER BY m.created_at DESC;

Update: I think also the DESC should be not in the GROUP BY, so added an ORDER BY

Answer 2

You don't need to join the tables. The conversations table is not needed at all:

SELECT m.message, m.created_at
FROM messages m
WHERE m.conversation_id = 5 AND
      m.created_at = (SELECT MAX(m2.created_at)
                      FROM messages m2
                      WHERE m2.conversation_id = m.conversation_id
                     );

Or, in this case, because you only want one row for one conversation, the simplest method is:

SELECT m.message, m.created_at
FROM messages m
WHERE m.conversation_id = 5 
ORDER BY m.created_at DESC
LIMIT 1;