osama yaccoub osama yaccoub - 6 months ago 14
SQL Question

ORDER BY trim(field) works on a server and not on other

I have this query :

SELECT count(DISTINCT trim(level1)) cnn
FROM table1
WHERE f1 != -1
ORDER BY trim(level1);`


It runs on one database perfectly and on another typical one it gives :


ORDER BY trim Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with
no GROUP columns is illegal if there is no GROUP BY clause


when I remove
ORDER BY trim(level1)
it works fine.

Can anyone explain why? I am using MySQL.

Answer

MySQL allow you to run non standard SQL queries for which the SELECT list, HAVING condition, or ORDER BY list refer to nonaggregated columns.

In your query you are ordering by a nonaggregated column:

SELECT count(DISTINCT trim(level1)) cnn
FROM table1
WHERE f1 != -1
ORDER BY trim(level1)

since you have no group by, in standard SQL you can only ORDER BY count(DISTINCT trim(level1)), but you are ordering by trim(level1) which is not aggregated (what's the purpose of it anyway?).

The reason why on one server it works and one other is not working is that one server has the ONLY_FULL_GROUP_BY disabled (so the query will run) and the other has it enabled.

For a full explanation about standard SQL92, standard SQL99, and how MySql handles it please see this manual page: MySQL Handling of GROUP BY

Comments