I have two SQLite tables like this:
AuthorId | AuthorName
----------------------
1 | Alice
2 | Bob
3 | Carol
... | ....
BookId | AuthorId | Title
----------------------------------
1 | 1 | aaa1
2 | 1 | aaa2
3 | 1 | aaa3
4 | 2 | ddd1
5 | 2 | ddd2
... | ... | ...
19 | 3 | fff1
20 | 3 | fff2
21 | 3 | fff3
22 | 3 | fff4
I want to make a SELECT query that will return the first N (e.g. two) rows for each AuthorId, ordering by Title ("Select the first two books of each author").
Sample output:
BookId | AuthorId | AuthorName | Title
------------------------------------------
1 | 1 | Alice | aaa1
2 | 1 | Alice | aaa1
4 | 2 | Bob | ddd1
5 | 2 | Bob | ddd2
19 | 3 | Carol | fff1
20 | 3 | Carol | fff2
How can I build this query?
(Yes, I found a similar topic, and I know how to return only one row (first or top). The problem is with the two).
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.