How can I limit number of results by a specific column in postgreSQL?
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 < 4group by user_name, item_name order by user_name, count_item desc;
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;