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.

sql mysql php

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Country State City Dropdown list in PHP MySQL PHP

Country state city dropdown using ajax in php. You'll learn how to populate country city state dropdown based on previous selection in php using ajax

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

Compare ScaleGrid MySQL vs. DigitalOcean Managed Databases - See which offers the best MySQL throughput, latency, and pricing on DigitalOcean across workloads.

Welcome Back the T-SQL Debugger with SQL Complete – SQL Debugger

Debug SQL stored procedures and develop your SQL database project with dbForge SQL Complete, a new add-in for Visual Studio and SSMS. When you develop large chunks of T-SQL code with the help of the SQL Server Management Studio tool, it is essential to test the “Live” behavior of your code by making sure that each small piece of code works fine and being able to allocate any error message that may cause a failure within that code.