查询为两个唯一字段中的每一个选择具有特定ID的行并计算第三字段的位置

Question

My relevant table structure is like so (with id being primary key and having autoincrement):

id | pid | type | distance | pspeed

Each pid (player id) has a unique row for every type and pspeed combination. For example, there is no row with a specific pid value, a specific type value, and more than one pspeed value. Likewise for pid and pspeed, there is not more than one row with the same type value.

My goal is to, for every type (there are 26 total), group by pspeed (there are 7 different pspeed values) and sort by distance descending until a specific pid value is found and return that row with the "rank" or "position" of that row. Basically I want to do a ORDER BY distance for each type and pspeed until a specific pid value is reached.

Is this possible via pure queries? I know how to do this with several queries and loops in a PHP script but I would like to be able to minimize the code I have to write. Unfortunately, I'm quite positive it would require SQL knowledge that is beyond me.

If counting up until a specific pid value is not possible I can just bake that into my PHP script to determine the "rank".

---

Edit: here is how I'm currently doing this (using CodeIgniter):

$res = array();
$q1 = $this->db1->get_where('uq_players', array('authid' => $authid), 1);
if($q1->num_rows()){
    foreach($this->jtype as $type => $value){
        foreach($this->pspeed as $speed){
            $i = 0;
            $this->db1->order_by('distance', 'desc');
            $q2 = $this->db1->get_where('uq_jumps', array('type' => $type, 'pspeed' => $speed));
            foreach($q2->result() as $row){
                $i += 1;
                if($row->pid === $q1->row()->id){
                    $res[] = self::create_array($row, $type, $row->wpn, $i);
                    break;
                }
            }
        }
    }
}

And some sample data.

Answer 1

You can first try geting a rowset of all different scores for a given player and all combinations of type and pspeed, then left join all other scores with the same type and pspeed but greater distance. If your player is ranked first for this combination you will get one row with nulls, otherwise you will get number of rows equal to number of players with greater distance, which incremented will give you the players rank.

SELECT t1.type, t1.pspeed, 1+SUM(t2.pid IS NOT NULL) as rank
FROM scores t1
LEFT JOIN scores t2 
  ON t1.type = t2.type AND t1.pspeed = t2.pspeed AND t1.distance < t2.distance
WHERE t1.pid = 1
GROUP BY t1.type, t1.pspeed

See Sqlfiddle example.