Order by result from another tables

Question

HI i have skills tables as below

Various user add skills to their profile. Now i want to list all the skills decreasing order of their uses. Like as below

Php(10) , ASP (5) , Perl(1)

Its means 10 user added php as their skill, 5 user ASP etc.

I have stored the skills in user table in skills column with comma separated

Answer 1

Try this:

select id, name
from (
    select *, (select sum(1) from user u where find_in_set(s.id, u.skills)) as cnt
    from skills s
) t
order by cnt desc
-- limit 20