I have a table with composite keys id and name, where id is foreign key to another table.

id name
1 a
1 b
1 c
2 b
2 f
3 b
3 c
3 f
4 f

And i need to sort which id-s have most matching 'name' values as id for example 1. Here, result would be [3, 2] (id 4 doesn't match one time), id3 matches 2 times (b and c) and id2 matches 1 time (b).

How would I query this?

Ideally it would return ordered pairs of (id, match_count).

CodePudding user response：

It can easily be done with a join and group by:

-- using id = 1 as reference

select t1.id, count(*) as match_count
from (select id, name from t where id <> 1) as t1
join (select name from t where id = 1) as t2 on t1.name = t2.name
group by t1.id
order by match_count desc