Jump to content
Larry Ullman's Book Forums

Chapter 7: Page 220: Coalesce Sql Error


Recommended Posts

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

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_post
FROM forums AS f
LEFT JOIN messages AS m
USING (forum_id)
GROUP BY (m.forum_id)
ORDER BY MAX(m.date_entered) DESC;

Link to comment
Share on other sites

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

 Share

×
×
  • Create New...