'Performing a batch select latest n in Postgres?

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
postgresql


Solution 1:[1]

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

Solution 2:[2]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rahul Biswas
Solution 2 sdgfsdh

Related Questions