CODEWITHSUNDEEP
sqloracle-database
Andy Xia
Andy Xia

Reputation: 41

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

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:

enter image description here

And this is the expected output

enter image description here

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

Upvotes: 1

Views: 994

Answers (2)

GMB
GMB

Reputation: 222382

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
)

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

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;

Upvotes: 1

Related Questions