Hello can you help me get the rank with custom ties? i have a table of Scores stores all the scores given by the judges.
+----+----------+-------------+--------+
| Id | judge_id |performer_id | score |
+----+----------+-------------+--------+
| 1 | 1 | 1 | 98 |
| 2 | 1 | 2 | 98 |
| 3 | 1 | 3 | 94 |
| 4 | 1 | 4 | 96 |
| 5 | 2 | 1 | 93 |
| 6 | 2 | 2 | 80 |
+----+----------+-------------+--------+
heres what the code i have searched.
SELECT
id
, judge_id
, performer_id
, score
, FIND_IN_SET(
score
, (SELECT
GROUP_CONCAT(DISTINCT score ORDER BY score DESC)
FROM
scores
WHERE
judge_id = 1
)
) AS rank
FROM
scores
WHERE
judge_id = 1
ORDER BY rank ASC
and the output of this is:
+----+----------+-------------+--------+------+
| Id | judge_id |performer_id | score | rank |
+----+----------+-------------+--------+------+
| 1 | 1 | 1 | 98 | 1 |
| 2 | 1 | 2 | 98 | 1 |
| 3 | 1 | 4 | 96 | 3 |
| 4 | 1 | 3 | 94 | 4 |
+----+----------+-------------+--------+------+
it is working but the output is not what i want. i want to get the ranking and ties like this.
+----+----------+-------------+--------+------+
| Id | judge_id |performer_id | score | rank |
+----+----------+-------------+--------+------+
| 1 | 1 | 1 | 98 | 1.5 |
| 2 | 1 | 2 | 98 | 1.5 |
| 3 | 1 | 4 | 96 | 3 |
| 4 | 1 | 3 | 94 | 4 |
+----+----------+-------------+--------+------+
where get all the rank of the tie then divide it by how many performer ties in the rank.
ex.
performer 1 score 98 rank 1
performer 2 score 98 rank 1
suppose that performer 2 should get rank 2
i want to compute it like
1+2 = 3 then divide it by 2 since 2 performers are tie in rank 1
1=2 = 3 / 2
answer is 1.5
im sorry for my english
but please can any one help me? im stuck at this problem.