MySQL - Get count() for each row in a group with different condition

Question

I have a high scores table scores: (id int, deviceid int, value int, board int). I'd like to display a position for a specific player. At the moment I'm doing this (in PHP):

I select all boards where the player has a score and the score itself:

select board, min(value) as value
from scores
where deviceid = 1234
group by board

Then for each row ($board,$value) I select:

select count(id) from scores
where board = $board and value < $value

by selecting number of rows for specific board with score less than specified value, I get the player's position (first player would get position 0, so it will be increased by 1 when displayed)

I know this is horribly unefficient, so I'm looking for a way to do it faster. It could possibly be done in a stored procedure with a cursor, but for 100 boards, I will need to do at most 1+100 selects.

I a nutshell, I would like to do something like this, in pseudo-sql:

select board, min(value) as val,
       count(id) from _this_group_ where value < val
from scores
where deviceid = 1234
group by board

Answer 1

SELECT scores.board, count(1) AS position
FROM scores
JOIN
  (SELECT board, MIN(value) AS value
   FROM scores
   WHERE deviceid = 1234
   GROUP BY board
  ) player_scores
ON scores.board = player_scores.board
WHERE scores.value < player_scores.value
GROUP BY scores.board

Answer 2

You can try something like:

SELECT a.board, b.val COUNT(1) AS cnt
FROM scores a
    INNER JOIN(
        SELECT board, MIN(value) as val
        FROM scores
        WHERE deviceid = 1234
        GROUP BY board
    ) b
     ON a.board = b.board
    AND a.value < b.val
WHERE a.deviceid = 1234
GROUP BY a.board;

or

SELECT a.board, MIN(a.value) as val,
       (SELECT COUNT(1)
        FROM scores b
        WHERE b.board = a.board AND
        b.value < MIN(a.value)) AS cnt
FROM scores a
WHERE a.deviceid = 1234;