How to select the first N rows of each group?

Solution 1:

You can do the counting using a correlated subquery:

SELECT b.BookId, a.AuthorId, a.AuthorName, b.Title
FROM Author a join
     Book b
     on a.AuthorId = b.AuthorId
where (select count(*)
       from book b2
       where b2.bookId <= b.BookId and b2.AuthorId = b.AuthorId
      ) <= 2;

For a small database this should be fine. If you create a composite index on Book(AuthorId, BookId) then that will help the query.

Solution 2:

There is alternative variant:

SELECT * FROM (
    SELECT * FROM BOOK, AUTHOR
    WHERE BOOK.AUTHORID = AUTHOR.AUTHORID
) T1
WHERE T1.BOOKID IN (
    SELECT T2.BOOKID FROM BOOK T2
    WHERE T2.AUTHORID = T1.AUTHORID
    ORDER BY T2.BOOKTITLE
    LIMIT 2
)
ORDER BY T1.BOOKTITLE