Their latest, front and center

Problem

The librarian is setting up a display by the front desk with the latest from each author: the book of theirs that was published most recently. The library stores only the year a book was published, so if an author's two most recent books are from the same year, both go on the display. Show the author's name, the title, and the year it was published, in that order, sorted by author and, within an author, by title. The book with no author isn't anyone's latest and stays on its shelf, and an author the library has no books by has nothing to put out.

What it has to return

This is what comes out with the data in the practice database this challenge uses. The column names don’t count, so name yours however you like: what counts is how many there are and the order they come in. Besides this one, the challenge has hidden tests with other data that are revealed when you submit your solution.

You start with this

SELECT author_id,
  MAX(published) AS latest
FROM books
GROUP BY author_id;