Performing a batch select latest n in Postgres?

Question

Suppose I have query for fetching the latest 10 books for a given author like this:

SELECT *
FROM books
WHERE author_id = @author_id
ORDER BY published DESC, id
LIMIT 10

Now if I have a list of n authors I want to get the latest books for, then I can run this query n times. Note that n is reasonably small. However, this seems like an optimization opportunity.

Is there are single query that can efficiently fetch the latest 10 books for n given authors?

This query doesn't work (only fetches 10, not n * 10 books):

SELECT *
FROM books
WHERE author_id = ANY(@author_ids)
ORDER BY published DESC, id
LIMIT 10

Does this answer your question? https://stackoverflow.com/a/37862028/12135804 lateral joins are a bit cryptic, but the best analogy I've heard is they're like a "foreach" — Optimum, Feb 04 '22 at 16:28
Does this answer your question? [Grouped LIMIT in PostgreSQL: show the first N rows for each group?](https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group) — Optimum, Feb 04 '22 at 17:20

score 2 · Answer 1 · answered Feb 04 '22 at 16:31

First provided author wise book where book is serialized by recent published date for generating a number using ROW_NUMBER() and then in outer subquery add a condition for fetching the desired result.

SELECT *
FROM (SELECT *
           , ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY published DESC) row_num
      FROM books
      WHERE author_id = ANY(@author_ids)) t
WHERE t.row_num <= 10

score 0 · Answer 2 · answered Feb 04 '22 at 17:51

0

SELECT b.*
FROM authors a
JOIN LATERAL (
  SELECT *
  FROM books b
  WHERE b.author = a.id
  ORDER BY b.published DESC, b.id
  LIMIT 10
) b ON TRUE
WHERE a.id = ANY(@author_ids)

answered Feb 04 '22 at 17:51

sdgfsdh

33,689
26
132
245

Performing a batch select latest n in Postgres?

2 Answers2