MySQL Forums
Forum List  »  General

only_full_group_by Query Rewrite Assist
Posted by: Matthew Devine
Date: October 12, 2018 12:51PM

So I'm trying to think of the best way to rewrite the following query as I'm getting the following error with a move of my dev environment to MySQL 5.7.

"Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.created' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

So the table contains messages between users and I'm looking to get the 5 most recent unique users that user 1234 has received from or sent messages to. And I want it order by the most recently created.

SELECT userId, created
FROM (
select uid as userId, MAX(CREATED) AS created FROM message WHERE puid = 1234 GROUP BY uid
UNION
select puid as userId, MAX(CREATED) AS created FROM message WHERE uid = 1234 GROUP BY puid
) AS a
GROUP BY userId
ORDER BY a.created DESC
LIMIT 5;


Thanks for the help

Options: ReplyQuote


Subject
Written By
Posted
only_full_group_by Query Rewrite Assist
October 12, 2018 12:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.