MySQL Forums
Forum List  »  Newbie

Tree behavior - Make this query compatible with mysql with sql_mode=only_full_group_by
Posted by: Patrick FAURE
Date: August 21, 2017 08:02AM

Hello,
I would like to get all records of the same level in a tree table.
The query below works fine with old version of MySQL but does work with the version Ver 14.14 Distrib 5.7.19 with this error message:

Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxxxx.n.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

How to get this query compatible with SQL_MODE=ONLY_FULL-GROUP-BY?

Thank you the your answer and time you spend on it.
Patrick

The query:

SELECT n.name,
COUNT(*)-1 AS level,
ROUND ((n.rgt - n.lft - 1) / 2) AS offspring
FROM tree AS n,
tree AS p
WHERE n.lft BETWEEN p.lft AND p.rgt
GROUP BY n.lft
ORDER BY n.lft;


with sql_mode=only_full_group_by

Options: ReplyQuote




Sorry, only registered users may post in this forum.

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.