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

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!

SELECT author_fname, author_lname, title, MIN(released_year) 
 FROM BOOKS 
 GROUP BY author_fname, author_lname;

Answer

SELECT r.author_fname, 
  r.author_lname, r.released_year, 
  r.title
FROM
 (SELECT author_fname, author_lname, 
      MIN(released_year) AS min_year FROM books
  GROUP BY author_fname, author_lname) l
 JOIN books r
   ON r.author_fname = l.author_fname 
   AND r.author_lname = l.author_lname
   AND r.released_year = l.min_year

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

SELECT author_fname, author_lname, title, MIN(released_year) 
 FROM BOOKS 
 GROUP BY author_fname, author_lname;

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.