0

I have a MySQL table that looks something like this:

GOALS
------------------------
   PLAYER   |   GOALS
------------------------
     001    |    30
     002    |    25
     003    |    25
     004    |    20

I want to have a rank-based scoring system that gives half-points for ties. In the above example, it should look like this:

GOALS
-----------------------------------
   PLAYER   |   GOALS   |   SCORE
-----------------------------------
     001    |    30     |    4
     002    |    25     |    2.5
     003    |    25     |    2.5
     004    |    20     |    1

In case of a three-way tie:

GOALS
-----------------------------------
   PLAYER   |   GOALS   |   SCORE
-----------------------------------
     001    |    30     |    5
     002    |    25     |    3
     003    |    25     |    3
     004    |    25     |    3
     005    |    20     |    1

How would something like this be done in mySQL?

Thanks!

g1derekl
  • 13
  • 2
  • Are you looking for pure MySQL answer or can other languages operating with MySQL be used such as PHP? – amaster Mar 10 '14 at 18:13
  • I'm using Node.js for my application, so that would be nice, too. I'm guessing you mean to iterate through the sorted goals list and add points to each one? – g1derekl Mar 10 '14 at 18:22

1 Answers1

2
SELECT a.player, a.goals, (SUM(a.goals > b.goals) + 1 + SUM(a.goals >= b.goals))/2 AS score
FROM goals a
CROSS JOIN goals b
GROUP BY player

DEMO

This works because SUM(a.goals > b.goals) + 1 is the minimum rank of all the tied players, while SUM(a.goals >= b.goals) is the maximum rank. So this averages the two ends of the range to get the score.

Barmar
  • 741,623
  • 53
  • 500
  • 612