24

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).

Bob_Gneu
  • 1,591
  • 1
  • 18
  • 30
Vladislav
  • 165
  • 1
  • 5
  • 15
  • Someone had a similar problem here http://stackoverflow.com/questions/9518900/how-to-find-teams-with-sql-command – t-clausen.dk Aug 01 '13 at 11:10
  • Please see ["Should questions include “tags” in their titles?"](http://meta.stackexchange.com/questions/19190/should-questions-include-tags-in-their-titles), where the consensus is "no, they should not"! –  Dec 14 '15 at 09:56

3 Answers3

20

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
15

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
fasked
  • 3,555
  • 1
  • 19
  • 36
  • I liked this answer better for my particular case, but MySQL didn't support "limit" inside "in" subquery, so I had to go with the other solution... :( upvoted anyway :) – msb Jul 04 '14 at 18:27
-1

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.booktitle
FROM author a
JOIN book b ON b.authorid = b.authorid
QUALIFY ROW_NUMBER() OVER (PARTITION BY a.authorid
ORDER BY   b.booktitle ASC) <=2
Bryan Nguyen
  • 7
  • 1