How to select the first N rows of each group?
You can do the counting using a correlated subquery:
SELECT b.BookId, a.AuthorId, a.AuthorName, b.TitleFROM Author a join Book b on a.AuthorId = b.AuthorIdwhere (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.
There is alternative variant:
SELECT * FROM ( SELECT * FROM BOOK, AUTHOR WHERE BOOK.AUTHORID = AUTHOR.AUTHORID) T1WHERE T1.BOOKID IN ( SELECT T2.BOOKID FROM BOOK T2 WHERE T2.AUTHORID = T1.AUTHORID ORDER BY T2.BOOKTITLE LIMIT 2)ORDER BY T1.BOOKTITLE
Here you go. Might be too late but I just saw the post. You can change the <=2 to match the n you need.
SELECT a.authorid, a.authorname, b.bookid, b.booktitleFROM author aJOIN book b ON b.authorid = b.authoridQUALIFY ROW_NUMBER() OVER (PARTITION BY a.authoridORDER BY b.booktitle ASC) <=2