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

Question

Hi,

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

Unfortunately, I don't have Postgres to hand to test this, but a query like the following should get you to the results that you want.

select user_name, item_name, item_count from
(
  select user_name, item_name, count(item_name) as item_count,
    dense_rank() over (order by count(item_name) desc) as item_rank
  from my_table 
  group by user_name, item_name
)
where item_rank <= 3;

Answer 2

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;