Top "Limit-per-group" questions

Query X rows with the greatest/least value per group, i.e. do the SQL LIMIT, but per group.

How to select the first N rows of each group?

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 | …

sql sqlite greatest-n-per-group limit-per-group
Average of latest N records per group

My current application calculates a point average based on all records for each user: SELECT `user_id`, AVG(`points`) AS …

mysql average greatest-n-per-group limit-per-group