Please help: Advanced MySQL Subquery

[axiom82]

I have a result table (which will be the subquery) that returns the count of all `users` who have a greater `count` than the value supplied.  The objective is to return the `position` of the user with u.`id` given this subquery condition.  Here I am using u.`count` as the conditional value.

 

SELECT COUNT(*)
FROM (
     SELECT `id`
     FROM `users`
     WHERE `count` > u.`count`
)

 

u.`count` is actually a value from the main query.  u.count is not accessible in my subquery.  How can I make it accessible.  Below was my best idea...

 

SELECT u.`id`,
(
     SELECT COUNT(*)
     FROM (
          SELECT `id`
          FROM `users`
          WHERE `count` > u.`count`
     ) tbl
) AS `position`
FROM `users` u
WHERE u.`id`=1

 

If you look in the code above, I get a MySQL error stating that u.`count` is unknown...yet it is known to the parent query.

 

Please help.

[roopurt18]

select `id`, count(*) as `count` from `users` having count(*) > (select count(*) from `users` where `id`=1)