sql ranking with custom ties

sql ranking with custom ties

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.

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.

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Building PHP apps using SQL Server on Windows

3 step: Building PHP apps using SQL Server on Windows

PhP MySQL Projects

Simpliv LLC, a platform for learning and teaching online courses. We basically focus on online learning which helps to learn business concepts, software technology to develop personal and professional goals through video library by recognized industry experts and trainers.

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL