Ranking

con.Open();
            string query = @"SET @rank=0;
               SELECT player_ID'Player ID',player_name'Player Name',player_nick'Nick Name',HP'High Score', @rank:=@rank+1 As Rank
               FROM player_profile ORDER BY HP DESC;";
            MySqlCommand cm = new MySqlCommand(query, con);
            cm.Parameters.AddWithValue("@1", ID);
            var re = cm.ExecuteReader();
            while (re.Read())
            {
                Label37.Text = re.GetInt32("rank").ToString();
            }
            con.Close();

i want to get the rank of the specific player's ID but instead i just get the last player's rank.
any help?