How to select the first N rows of each group?

by

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.

More Related Contents:

  1. Select first row in each GROUP BY group?
  2. Get top 1 row of each group
  3. Optimize GROUP BY query to retrieve latest row per user
  4. how do I query sql for a latest record date for each user
  5. SQLite Reset Primary Key Field
  6. Update table values from another table with the same user name
  7. How do I dump the data of some SQLite3 tables?
  8. Select info from table where row has max date
  9. SQLite – How do you join tables from different databases?
  10. Simple Query to Grab Max Value for each ID
  11. Does SQLite3 not support foreign key constraints?
  12. How to select id with max date group by category in PostgreSQL?
  13. Query to get records based on Radius in SQLite?
  14. How do I add a foreign key to an existing SQLite table?
  15. How to use SQL Order By statement to sort results case insensitive?
  16. Can I do a max(count(*)) in SQL?
  17. Select a Column in SQL not in Group By
  18. Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
  19. sqlite alter table add MULTIPLE columns in a single statement
  20. Is there AUTO INCREMENT in SQLite?
  21. Get month from DATETIME in sqlite
  22. How do I import .sql files into SQLite 3?
  23. How can I select rows with most recent timestamp for each key value?
  24. sqlite equivalent of row_number() over ( partition by …?
  25. Optimize groupwise maximum query
  26. Does SQLite3 have prepared statements in Node.js?
  27. SQlite: select into?
  28. IF() statement alternative in SQLite
  29. Conditional lead/lag function PostgreSQL?
  30. Select latest row for each group from oracle

Leave a Comment