CODEWITHSUNDEEP
sqlpostgresqllimit
Huuuze
Huuuze

Reputation: 16367

Query to limit the number of results per record based upon date

I have the following tables in my Postgresql database (trunc'ed for the sake of simplicity):

Person (id, name)
Book (id, person_id, title, check_out_date)

I need a query that will return only the three most recent books based upon check_out_date per person. In other words, if a person has 5 books, I only want the query to return the 3 most recent book records.

UPDATE: Since there's been some confusion, let me clarify. If my Postgresql database has 100 Person records, I want to see the Book records for each person, limiting what's returned to 3 books per person. So, if a Person has 5 books, I only want the first 3 returned in the query.

Upvotes: 0

Views: 455

Answers (1)

Mark Wenzel
Mark Wenzel

Reputation: 776

You can use a with clause that selects the order of the books by person descending and select from those where the books are in the top 3.

with tmp as (
  select
    b.id,
    b.person_id,
    b.title,
    b.check_out_date,
    row_number() over (partition by b.person_id order by b.check_out_date desc) as order_pos
  from
    book b
)
select
  t.id,
  t.person_id,
  t.title,
  t.check_out_date
from
  tmp t
where
  t.order_pos <= 3
;

Upvotes: 1

Related Questions