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

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

I don't know about MySQL, but in transact SQL the DESC goes in the ORDER BY — BClaydon, Sep 27 '19 at 23:49
You need a aggregate function https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html for m-message aor a group by . Furtehr DESC at the end is wrong it belongs to order by — nbk, Sep 27 '19 at 23:53
Possible duplicate of [SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql\_mode=only\_full\_group\_by](https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc) The very first thing you should do is a basic search on the relevant portion of the error message. I found the duplicate (among many others) with a simple search for *Expression #1 of SELECT list is not in GROUP BY clause*. — Ken White, Sep 27 '19 at 23:55

Julian · Answer 1 · 2019-09-27T23:56:21.683

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

edited Sep 27 '19 at 23:56

answered Sep 27 '19 at 23:54

Julian

33,915
22
119
174

perfect! I'll accept when it lets me – ss_matches Sep 27 '19 at 23:55
mysql 8/ Workbench doesn't like DESC at tihis position – nbk Sep 27 '19 at 23:55
@nbk think you're right, so updated the post – Julian Sep 27 '19 at 23:58

score 0 · Answer 2 · answered Sep 28 '19 at 00:11

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;

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

2 Answers2