How to group the result of GROUP BY in a query?

Question

I want to SELECT 5 most sold articles.

SELECT ID, shop, article_nr, count(*) as count
FROM table
GROUP BY shop, article_nr
ORDER BY count DESC
LIMIT 5

Results of this query:

ID | shop   | article_nr | count
--------------------------------------
71 | amazon | 123        | 280
98 | amazon | 223        | 170
35 | amazon | 323        | 99
7  | ebay   | 456        | 71
11 | amazon | 789        | 49
4  | ebay   | 032        | 10

How to group this results by the column 'shop' in a second step (maybe edit the query)?

It should look like this (only 'one' shop in the top 5):

ID | shop       | article_nr  | count
--------------------------------------
71 | amazon     | 123         | 280
7  | ebay       | 456         | 71
.. | shop_x     | ...         | 55
.. | shop_y     | ...         | 40
.. | shop_z     | ...         | 37

Can someone help me?

Answer 1

This is what you want

SELECT id, shop, article_nr, count FROM table n 
WHERE count = (SELECT MAX(count) FROM table GROUP BY shop HAVING shop = n.shop) LIMIT 5;

Answer 2

Popular and well described answer to your problem is right here: https://stackoverflow.com/a/612268/1803682

Specifically how do I select AggregateFunction(column) distinct on (other column)

I will be happy to help more if you want to play with this in a SQL Fiddle and post it.