How can I limit number of results by a specific column in postgreSQL?

Question

I have a table with user items. Each user may have several types of items, and may have each item more than once. I want to see how many items of each type each user have. So I use the following query:

select user_name, count(item_name) as "count_item", item_name 
from my_table 
group by user_name, item_name 
order by user_name, count_item desc;

So I get something like this:

user_name | count_item  | item_name
----------+-------------+-----------
User 1    | 10          | item X
User 1    | 8           | item Y
User 2    | 15          | item A
User 2    | 13          | item B
User 2    | 7           | item C
User 2    | 2           | item X

etc.

Now, I want to see only the first 3 items of each user. In the above example, for User 1 I want to see item X and Y, and for User 2 I want to see items A, B and C.

How can I acheieve this?

Thanks!

Answer 1

Use PARTITION BY. Something like this should work:

select user_name, count_item, item_name 
from (select user_name, count(item_name) as "count_item", item_name 
    row_number() over (partition by user_name order by count_item desc)
    from my_table)
where row_number < 4
group by user_name, item_name 
order by user_name, count_item desc;