CODEWITHSUNDEEP
sqlpostgresql
fbucek
fbucek

Reputation: 1667

One query to return multiple rows for multiple values with limit

Assume you have database with author and book tables.

I want to get 10 most recent books for each author.

Normally it is N+1 SQL queries.

I want to have only 1 instead of N queries, to get 10 most recent books for list of authors. ( maybe it does not make sense in case of performance / but I do not know )

Is there a way to make SQL query to return most recent books for list of authors? Thanks.

Upvotes: 1

Views: 1078

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

An alternative to window functions using lateral join:

select a.authorid, b.*
from author a 
cross join lateral 
(
  select * from books 
  where books.authorid = a.authorid
  order by releasedate desc
  limit 10
) b;

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

You can try using row_number()

select * from
(
select *,row_number() over(partition by a.authorid order by releasedate desc) as rn
from author a join books b on a.authorid=b.authorid
)f where rn<=10

Upvotes: 2

Related Questions