How to retrieve a non group-by column in SQL?

Question

Books table has id, author_fname, author_lname, title, released_year. To display the author's first and last name and the minimum year of the publication of his books, the query will be

SELECT author_fname,
       author_lname,
       Min(released_year)
FROM books
GROUP BY author_lname,
         author_fname;

But, what if we also want to display the title of the first book of every author? Is it possible to do it without using nested queries?

I tried this:

SELECT author_fname, 
       author_lname, 
       title,
       Min(released_year) 
FROM   books 
GROUP  BY author_lname, 
          author_fname;

but it gives title of wrong book. How do I fix the query?

Answer 1

You can use correlated subquery :

select b.*
from books b
where b.released_year = (select min(b1.released_year) 
                         from books b1 
                         where b1.author_lname = b.author_lname and b1.author_fname = b.author_fname
                        );