CODEWITHSUNDEEP
mysql
Laurent
Laurent

Reputation: 1048

MySQL count "IN" statement matches

In a query like this:

SELECT ID FROM table 
WHERE field 
IN (SELECT field2 FROM table2 WHERE condition)

How I can order the returned rows by the number of records matching in the IN statement ?

Upvotes: 0

Views: 56

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I am going to assume that you mean by the number of distinct values in table 2 that match.

select t.ID, count(t2.field2) 
from table t left outer join
     (select distinct field2
      from table2
      where condition
     ) t2
     on t.field = t2.field2
group by t.id
order by count(t2.field2) desc;

Why is it structured like this? The left outer join ensures that all rows from table are in the result set. The distinct ensures that duplicate rows in table2 do not result in duplicate rows in the output. count(t2.field2) counts the number of matches in the second table, so it will be 0 when there is no match (as opposed to count(*) which would be 1 in that case).

This is assuming that id is unique in table.

Upvotes: 1

Tin Tran
Tin Tran

Reputation: 6202

try JOIN-ing ON where those fields are equal and then do a COUNT() like below, then you can ORDER BY that COUNT() (representing occurences of field = field2)

SELECT table.ID,COUNT(*) as occurrence 
FROM table INNER JOIN table2
ON table.field = table2.field2
WHERE condition
GROUP BY table.ID
ORDER BY occurrence

Upvotes: 0

Related Questions