'Getting top 5 most associated item [duplicate]

I have a Postgres database with a many-to-many association table that's similar to what's down below.

id | item_id | item_tag_id
 1     101       3
 2     102       3
 3     103       1
 4     104       2
 5     105       2

How can I get the top 5 most associated item_tag_id?

sqlpostgresql


Solution 1:[1]

Basically, group by item and order by the count of rows (= count of items in a proper many-to-many design):

SELECT item_id, count(*)
FROM   assoc_tbl
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  5;

There is a remaining corner-case: how to break ties for the top 5? Either define criteria (resulting in more ORDER BY expressions), or consider WITH TIES. See:

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions