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).
>Solution :
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