mySQL , Find the most recent book that each author has written

Question

so I have the question of Find the most recent book that each author has written. So this is the tables that I'm using:

And this is the expected output

So what I got so far is :

select unique fname || ' ' || lname "Author", max(pubdate) "Date 
Pub"
from books join bookauthor using (isbn)
join author using (authorid)
group by fname, lname
order by 2,1;

and that gives me the output

Author            Date Pub
--------------------- ---------
JUAN ADAMS        04-JUN-03
TINA PETERSON     04-JUN-03
JACK BAKER        17-JUL-04
OSCAR FIELDS      17-JUL-04
LISA PORTER       21-JAN-05
WILLIAM WHITE     21-JAN-05
LISA WHITE        01-MAY-05
ROBERT ROBINSON   08-NOV-05
JAMES AUSTIN      31-DEC-05
JANICE JONES      01-MAR-06
TAMARA KZOCHSKY   18-MAR-06
SAM SMITH         11-NOV-06

The problem is when I add title to my query, every title shows up and not just the ones with the most recently published.

select unique fname || ' ' || lname "Author Name", title , pubdate
from books join bookauthor using (isbn)
join author using (authorid)
order by 1,2;

Author Name           TITLE                      PUBDATE
--------------------- ------------------------------ ---------
JACK BAKER            COOKING WITH MUSHROOMS     28-FEB-04
JACK BAKER            PAINLESS CHILD-REARING     17-JUL-04
JAMES AUSTIN          DATABASE IMPLEMENTATION    04-JUN-03
JAMES AUSTIN          HOLY GRAIL OF ORACLE       31-DEC-05
JANICE JONES          E-BUSINESS THE EASY WAY    01-MAR-06
JANICE JONES          REVENGE OF MICKEY          14-DEC-05

Answer 1

Try in this way:

select unique fname || ' ' || lname "Author Name", title , pubdate
from books b
where isbn in (
  select ba.isbn from (
    select ba.isbn from bookauthor ba
    join author a using (authorid)
    order by pubDate
    LIMIT 1
  )t
)
order by 1,2;

Answer 2

With MySQL 8.0 (or almost any version of Oracle), this is straight forward using ROW_NUMBER() :

SELECT * FROM (
    SELECT
        CONCAT(a.fname, ' ', a.lname),
        b.title,
        b.pubdate,
        ROW_NUMBER() OVER(PARTITION BY b.authorid ORDER BY b.pubdate DESC) rn
    FROM
        books b
        JOIN bookauthor ba using (isbn)
        JOIN author a using (authorid)
) x WHERE rn = 1

In RDBMS that do no support window functions (like MySQL 5.x), using a NOT EXISTS condition might be more efficient than aggregation :

SELECT
    CONCAT(a.fname, ' ', a.lname),
    b.title,
    ROW_NUMBER() OVER(PARTITION BY authorid ORDER BY pubdate DESC) rn
FROM
    books b
    JOIN bookauthor ba using (isbn)
    JOIN author a using (authorid)
WHERE NOT EXISTS (
    SELECT 1
    FROM books b1 JOIN bookauthor ba1 using (isbn)
    WHERE ba1.authorid = b.authorid AND b1.pubdate > b.pubdate
)