Skip to content
Advertisement

How to retrieve a column in SQL that cannot be GROUPED BY

enter image description here

SQLFIDDLE: sqlfiddle.com/#!9/a83823/4

I want to retrieve the first book released by each author in the table. I want to run a query that displays the author’s name, the title of the earliest book and the year it was released. I tried using the code below but I can see why it is causing an error but I cannot find a way around this. Any help will be appreciated!

Advertisement

Answer

Should do it. The usual trick is to write a query that selects the max/min/sum from each group and then you perform a join.

Your query

is actually illegal! Although there’s a MySQL option that allows you to do this sort of stuff but generally it’s invalid to select a column that does not appear in the GROUP BY clause. Also, if you do select a column that you don’t group by then it’s value is essentially undefined (although most engines will set it to the first value they see which means you get the value of the row you inserted first). Some installations have this option disabled per default while some seem to have it enabled by default but anyway… you’re not supposed to select a column that does not appear in your GROUP BY clause.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement