Jump to content
Larry Ullman's Book Forums

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

Share this post


Link to post
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;

Share this post


Link to post
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!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...