Handling ties when ranking from the highest to the lowest

Question

I am trying to make a ranking manager for a small project.The totals are stored in the database.I can easily get the max and min using mysql and also arrange the records descending.The problem comes in when there is a tie.I need to show a tie in the form:1,2,3,3,4,5,6,7,7,7,7, etc.The repeated numbers will show the ties.I have been thinking of ways of achieving the above but i need more ideas;mine is seems long and complicated.

Can anybody share his/her idea of doing the ties.

Answer 1

Could you not just do that in SQL as well? The records with duplicate ranks will still get output, however the order within the tie will be undefined.

SELECT id, rank FROM mytable ORDER BY rank ASC;

Ah, looks like I misunderstood the question, thanks Matchu. This could still be achieved with a relatively simple SQL query. The key is using a subquery to count the distinct scores that have already passed.

SELECT id, score, (
  SELECT COUNT(DISTINCT score) + 1 
  FROM mytable 
  WHERE score < outerscore.score 
  ORDER BY score ASC
) AS rank
FROM mytable as outertable
ORDER BY score ASC;

Answer 2

$data = array(
  'A'=>19,'B'=>18,'C'=>17,'D'=>17,'E'=>16,'F'=>15
);

$rank = 0;
$lastScore = PHP_INT_MAX;
foreach( $data as $name=>$score ) {
  if ( $lastScore !== $score ) {
    $lastScore = $score;
    $rank += 1;
  }
  printf("%s %d (%d)\n", $name, $score, $rank);
}

prints

A 19 (1)
B 18 (2)
C 17 (3)
D 17 (3)
E 16 (4)
F 15 (5)