First, I don't want to use statement
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
to turn full group by off as it is enabled by default, I believe it is for good purpose. However, it also gives me the headache.
I have a very simple 2 database tables in MySQL:
chat and chat_message. chat has many chat messages. chat has a column called status
Now I want to find all the chat record id that has status='active', order by it's chat messages send date in one SQL statement
Therefore, I created the following SQL:
Select chat_messages.chat_id from chat_messages, chats where chats.status='active' and chat_messages.chat_id=chats.id group by chat_messages.chat_id order by chat_messages.send_date desc
OK, now when I execute the above SQL, I get error message:
[Code: 1055, SQL State: 42000] Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'chat_messages.send_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
If I put send_date into group by, the result simply will return multiple dupe chat record as each chat table record has multiple chat message records, and each chat message record has different send_date value.
OK now the question is: Without turning that only_full_group_by off, how to achieve the purpose in one sql statement?
Thank you.
** Update **
Not sure why stackoverflow mark the question as duplication of another unnecessary posts. It is clearly different from that question as this one is trying to address the error message that when ONLY_FULL_GROUP_BY is turned on.
The query is used to be pretty much straightforward in the past before introducing full group by but now it starts giving error. I hope the site knows there is no one size fit for all and stop making new problems as dupe with out dated solutions in the past as it does not help but only misleading others.
I want to find all the chat record id that has status='active', order by it's chat messages send date in one SQL statement
I understand that you want to order chats by their latest message. If so, you can do:
select c.*
from chat c
where c.status = 'active'
order by (select max(cm.send_date) from chat_message cm where cm.chat_id = c.id)
If you want to also display the date of the last message, we can move the correlated subquery to the select clause:
select c.*,
(select max(cm.send_date) from chat_message cm where cm.chat_id = c.id) last_send_date
from chat c
where c.status = 'active'
order by last_send_date
If all you want is the id of the chat (not other chat masterdata), you don't even need to look at the chat table:
select chat_id
from chat_message
group by chat_message
order by max(send_date)