Getting the first 3 children(items) for each owner in a list of primary keys

Question

I have two tables Item an Owner, an a Foreign Key from Item to Owner, owner_id. I'm using PostgreSQL 10.

For each owner I want to get the first 3 items if exist.

select  "items_item"."name" 
FROM "items_item" 
WHERE ("items_item"."owner_id" IN (64, 65, 130, 99, 43, 140, 108, 143, 115, 154)) 
ORDER BY LIMIT 3

Clearly is not ok, because is selecting just 3 items from all owners, not a maximum 3 items per owner .

Answer 1

You can use window functions to solve this kind of issues, something like:

select item_name
from (
    select name as item_name
         , row_number() over (partition by owner_id
                              order by id) as rn
    from items_item
    where owner_id in (64, 65, 130, 99, 43, 140, 108, 143, 115, 154)
) as t
where rn <= 3

I updated to order by id in window clause, according to comment