Mysql select query count until reach the condition

Question

I have lists of users with his points and game id. I need to find the rank of the specified user based on the game order by the maximum lb_point. Below is my table.

lb_id    user_id   game_id   room_id   lb_point
------------------------------------------------
1         1        2         1         670     
2         1        1         2         200     
3         1        2         2         650     
4         1        1         1         400     
5         3        2         1         700     
6         4        2         5         450     
7         2        1         3         550     
8         2        1         1         100     
9         1        1         1         200     

I have already done this by using PHP code and its working fine as follows.

$game_id = 2;
$user_id = 1;
$query_rnk = $this->db->query('SELECT user_id AS uid FROM leader_board WHERE game_id = "'.$game_id.'" GROUP by user_id ORDER BY lb_point DESC');
        if ($query_rnk->num_rows() > 0){
            $j=1;
            foreach($query_rnk->result() as $row_rnk){
                if($row_rnk->uid == $user_id){
                    $rnk_status = 1;
                    break;
                }
            $j++;
            }
            if($rnk_status == 1){
                $resp['rank'] = $j;
            }
        }

Answer is: 2.

But i need to find this by using one query. Any idea?

Answer 1

You could do with a select counting the number of rows of a proper select

select count(*) 
from (
      select distinct user_id
      from leader_board 
      where   lb_point >= (select  max( lb_point )
             from leader_board 
             where user_id = 1   
             and game_id = 2 )
      and game_id = 2
) t