guvna Posted November 21, 2017 Share Posted November 21, 2017 Page 220 syntax: SELECT f.name, COALESCE(MAX(m.date_entered), 'N/A') AS last_post FROM forums AS f -- my tablename is 'forums'. LEFT JOIN messages AS m USING (forum_id) GROUP BY (m.forum_id) ORDER BY m.date_entered DESC; Results in this error: #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'rich_forum.f.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Link to comment Share on other sites More sharing options...
Larry Posted November 22, 2017 Share Posted November 22, 2017 Hmmm...what version of MySQL are you using? Link to comment Share on other sites More sharing options...
guvna Posted November 22, 2017 Author Share Posted November 22, 2017 MySQL Version: 5.7.14 - Port defined for MySQL: 3306 Link to comment Share on other sites More sharing options...
guvna Posted November 30, 2017 Author Share Posted November 30, 2017 I've shown the error to a friend who works with Microsoft SQL and he suggests the syntax in the book is missing some elements. Below is code that he suggested to try and it works. MAX is just an example to show the code working: SELECT MAX(f.name),COALESCE(MAX(m.date_entered), 'N/A') AS last_postFROM forums AS fLEFT JOIN messages AS mUSING (forum_id)GROUP BY (m.forum_id)ORDER BY MAX(m.date_entered) DESC; Link to comment Share on other sites More sharing options...
Larry Posted December 1, 2017 Share Posted December 1, 2017 Unfortunately MS SQL doesn't necessarily support the same syntax and functions as MySQL. If you look at the image in the book (Figure D, page 220), that command did work exactly as entered for my version of MySQL at the time of the book's writing. Although I just reran it using a more current version of MySQL and now I'm getting the same error as you. Looks like this was a change in MySQL 5.7.5: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by This article talks about the issue more: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html I tried a couple of things but didn't come up with the obvious answer (aside from disabling ONLY_FULL_GROUP). Will investigate more! Link to comment Share on other sites More sharing options...
Recommended Posts