sql return only max date for each id

Question

I have a database with one table that looks like this

Books(id, title, author_id, date)

I am using type DATE for the date field.

What the query is looking to do is return only the most recent book from each author, so even for the books that have the same author_id, only return the book that has the most recent date field for that author. So for all books find the most recent book from each author, returning title, author_id, and date.

I believe I will need to use the MAX function with a GROUP BY author_id....or perhaps include a HAVING max(date)? Sort of a side note is it possible when returning the date to have it be age in years it has been released up to 1 decimal point? (ex. 3.1 if book date was just over 3 years old?).

Answer 1

You can use this query:

SELECT *
FROM Books
WHERE (author_id, date) IN (SELECT author_id, MAX(date)
                            FROM Books
                            GROUP BY author_id)

subquery will return, for every author, the maximum date of a book. Outer query will return all books for every author with the maximum date.

Please notice that this could return more than one book for every author if they are published in the same maximum date.