MySQL ORDER BY only returns one row -

this code :

select *  event_list  interest in    (    select interest_name     interest     interest_id in       (       select interest_id        `user's interests`        p_id=pid , is_canceled=0      )   ) order count(eid) desc 

i don't use group by clause still 1 row. when removing order by clause correct rows (but not in right order).

i'm trying return view (named event_list) sorted common eid (event id), want see every row without grouping.

i suspect query want more this:

select el.*,        (select count(*)         interest join              userinterests ui              on ui.is_canceled = 0 , ui.p_id = i.id         el.interest = i.interest_name        ) cnt event_list el order cnt desc; 

it bit hard tell without sample data , better formed query. notes:

• don't use special characters in table , column names. having escape names merely leads queries harder read, write, , understand.
• qualify column names, know tables columns come from.
• use table aliases -- queries easier write , read.
• the where clause filtering. description of problem doesn't seem involve filtering, ordering.
• any time use aggregation function, query automatically becomes aggregation query. without group by, 1 row returned.
• give foreign keys same names primary keys, possible.