1

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 .

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

| improve this answer | |
  • order by id, or date (I have a field date) – user3541631 Oct 29 '18 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.