Getting top 5 most associated item

Question

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?

This is unclear. What does most-associated mean? Highest number of tags? Most-used tags? — t56k, Sep 19 '21 at 19:52

Erwin Brandstetter · Accepted Answer · 2022-03-21T05:27:39.920

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:

Getting top 5 most associated item

1 Answers1