Naren Naren - 4 months ago 40
MySQL Question

Mysql select query count until reach the condition

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

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
Comments