sql - per - ¿Cómo seleccionar las primeras N filas de cada grupo?

sql last

Question

Tengo dos tablas SQLite como esta:

 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

Quiero hacer una consulta SELECT que devolverá las primeras N (por ej., Dos) filas para cada AutorId, ordenando por Título ("Seleccione los primeros dos libros de cada autor").

Muestra de salida:

 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

¿Cómo puedo construir esta consulta?

(Sí, encontré un tema similar y sé cómo devolver solo una fila (primera o superior). El problema es con los dos).

Answer 1

Hay una variante alternativa

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

Answer 2

Puede hacer el conteo usando una subconsulta correlacionada:

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;

Para una pequeña base de datos esto debería estar bien. Si crea un índice compuesto en Book(AuthorId, BookId) , eso ayudará a la consulta.

Answer 3

Aqui tienes. Podría ser demasiado tarde, pero acabo de ver la publicación. Puede cambiar <= 2 para que coincida con la n que necesita.

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