Home Query with GROUP BY and ORDER BY not working when multiple columns in SELECT are chosen
Reply: 0

Query with GROUP BY and ORDER BY not working when multiple columns in SELECT are chosen

user1681
1#
user1681 Published in April 20, 2018, 10:49 am

I'm updating an old website and one of the queries isn't working anymore:

SELECT * FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2

I noticed if I dropped the GROUP BY it works, but the result set doesn't match the original:

SELECT * FROM tbl WHERE col1 IS NULL ORDER BY col2

So I tried reading up on GROUP BY in the docs to see what might be the issue, and it seemed to suggest not using * to select all the fields, but explicitly using the column name so I tried it with just the column that was being ordered and grouped:

SELECT col2 FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2

Which works but after looking through the code the query requires 2 columns in the query so whoever added * was overdoing it, but if I add that column produces an error, similarly adding a third column produces the same error:

SELECT col2, col3 FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2
SELECT col1, col2, col3 FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2

Can anyone tell me why this last query doesn't work? I can't decipher why from the docs, but this is the minimum query required to get the result set I need.

Running the query in Adminer I get this error

Error in query (1055): Expression #2 of SELECT list is not in GROUP BY 
clause and contains nonaggregated column 'name.table.column' 
which is not functionally dependent on columns in GROUP BY clause; this is 
incompatible with sql_mode=only_full_group_by
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.458983 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO