True ranking in mySQL 1

maharg · Nov 7, 2009

Hi

I'm trying to create a mySQL table which internally calculates true ranking. See below ...

Name Time Rank
mike 20.5 1
fred 20.9 2
dave 21.5 =3
stan 21.5 =3
eddy 21.5 =3
paul 22.3 6
mick 23.7 7
vick 24.6 =8
olly 24.6 =8
dick 25.8 10

I can do this in php by saving a list of variables and comparing each to the next and previous, and adding the = sign, then re-writing to the db, but I'd like to do it in mySQL itself if possible.

See attached for an example of my php ranking method.

Thanks in advance,

graham

maharg · Nov 7, 2009

Oops! The php file is attached

maharg · Nov 7, 2009

Code:

<html><head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
<title>Evening League event ranking</title>
<style>
@media screen
{
body{background-color:#ffffff}
table{width:95%}
table.threecolumns{width:95%}
td.title{border-width:0px; font-size:40px; font-family:sans-serif; font-weight:bold; background-color:#ffffff; color:#ff0000}
tr.header {font-size:10px; font-family:sans-serif; font-weight:bold; background-color:#bb0000; color:#ffffff}
tr.lightgrey {font-size:10px;font-family:sans-serif;background-color:#eeeeee; color:#000000}
tr.darkgrey {font-size:10px;font-family:sans-serif;background-color:#cccccc; color:#000000}
td {border-style:solid; border-width:1px; border-color:#000000;}
td.noborder {border-style:solid; border-width:0px; border-color:#000000;}
td.first{width:7%}
td.second{width:66%}
td.third{width:12%}
td.fourth{width:15%}
}
@media print
{
td.title{border-width:0px;font-size:28pt; font-family:sans-serif; font-weight:bold}
tr.header {font-size:8pt; font-family:sans-serif; font-weight:bold}
tr.lightgrey {font-size:8pt;font-family:sans-serif}
tr.darkgrey {font-size:8pt;font-family:sans-serif}
td {border-style:solid; border-width:1px}
td.noborder {border-style:solid; border-width:0px}
td.first{width:7%}
td.second{width:66%}
td.third{width:12%}
td.fourth{width:15%}
}
</style>
</head>

<body>
<center>

<!---------- PHP RANKING CODE HERE  --------->
<?php
// True rider ranking test script - G.Laming 31 Nov 2009
$t0=0;  //// Set below minimum expected time




$t1=22.005;
$t2=22.010;
$t3=22.015;
$t4=22.020;
$t5=22.025;
$t6=22.035;
$t7=22.035;
$t8=22.035;
$t9=22.045;
$t10=22.050;
$t11=22.055;
$t12=22.060;
$t13=22.065;
$t14=22.070;
$t15=22.075;
$t16=22.085;
$t17=22.085;
$t18=22.090;
$t19=22.095;
$t20=22.100;
$t21=22.105;
$t22=22.110;
$t23=22.115;
$t24=22.125;
$t25=22.125;
$t26=22.125;
$t27=22.135;
$t28=22.140;
$t29=22.145;
$t30=22.150;
$t31=22.155;
$t32=22.160;
$t33=22.165;
$t34=22.170;
$t35=22.175;
$t36=22.180;
$t37=22.185;
$t38=22.190;
$t39=22.195;
$t40=22.200;
$t41=22.205;
$t42=22.210;
$t43=22.215;
$t44=22.220;
$t45=22.225;
$t46=22.230;
$t47=22.235;
$t48=22.240;
$t49=22.245;
$t50=22.250;
$t51=22.255;
$t52=22.260;
$t53=22.265;
$t54=22.270;
$t55=22.275;
$t56=22.280;
$t57=22.285;
$t58=22.290;
$t59=22.295;
$t60=22.300;
$t61=22.305;
$t62=22.310;
$t63=22.315;
$t64=22.320;
$t65=22.325;
$t66=22.330;
$t67=22.335;
$t68=22.340;
$t69=22.345;
$t70=22.350;
$t71=22.355;
$t72=22.360;
$t73=22.365;
$t74=22.370;
$t75=22.375;





$t76=100; /// set above max expected time

//Set up initial conditions
$rr1=1;
$rr0=0;
$truerank0=0;
$truerank2=2;
$nudge0=0;

///////////////////////////////////////////////////

$i=1; 
echo "<table border=0>\n\n";
while($i<=75)
{
////////////////////////////// START OF LOOP
////////////////////////////// Database queries would go here
$lower=$i-1;
$higher=$i+1;

////////////////////// Alternate those row colours. Because the columns will have odd numbers of riders, typically 25, we need to use 2 references
if($i % 2)
{ $rowcoloura= "lightgrey";$rowcolourb= "darkgrey";}
else
{ $rowcoloura= "darkgrey"; $rowcolourb= "lightgrey";}



// Rank_raw calculation
if
(${"t$i"}>${"t$lower"})
{${"rr$i"}=(floor(${"rr$lower"}+1));}
else
/// This bit keeps track of how many riders share the same rank, by adding 0.001 to each rank
{${"rr$i"}=(${"rr$lower"}+0.001);}

// Nudge calculation, to get the next slowest persons rank correct. See how many 0.001s have been accumulated and add.
${"nudge$i"}=round(1000*(${"rr$i"}-floor(${"rr$i"})));

//True rank calculation
if((floor(${"rr$i"}))==(floor(${"rr$lower"})))
{${"truerank$i"}=${"truerank$lower"};}
else
{${"truerank$i"}=(${"truerank$lower"}+1+${"nudge$lower"});}

// Now add the = sign if needed
if ((${"t$i"}==${"t$lower"})or(${"t$i"}==${"t$higher"}))
{${"truerankfinal$i"}= ("=".${"truerank$i"});}
else{${"truerankfinal$i"}=${"truerank$i"};}


///// Now we want to start building the three side-by-side tables.
///// We build the top of the first table before we write any data, so do this only while $i is less than 2


if($i<2){
echo "<tbody><tr><td colspan=\"3\" class=\"title\"><center>Bedfordshire Road Cycling Club - Evening League</center></td></tr>\n";
echo "<tr align=\"center\"><td class=\"noborder\"><!-----Column 1--------------------------------------------------->\n";
echo "<table class=\"threecolumns\"><tbody>";
echo "<tr class=\"header\"><td class=\"first\">Rank</td><td class=\"second\">Name</td><td class=\"third\">Time</td><td class=\"fourth\">Speed</td></tr>\n";}
else {echo "";}
///// CHOOSE LIMITS TO SUIT TABLE LENGTH !!
if (($i>=1)&&($i<=25))
{echo "<tr class=\"".$rowcoloura."\"><td class=\"first\">".${"truerankfinal$i"}."</td><td class=\"second\">Person's Name goes here</td><td class=\"third\">".number_format(${"t$i"},3)."</td><td class=\"fourth\">XX.XXX</td></tr>\n";}
else if (($i>=25)&&($i<=26))
{echo "</tbody></table><!---End--Column1-------------------------------------------------------></td>\n\n\n";}
else {echo "";}
///// End of First table

///// Now do 2nd third of field
if ($i==26){
echo "<td class=\"noborder\"><!-----Column 2--------------------------------------------------->\n";
echo "<table class=\"threecolumns\"><tbody>";
echo "<tr class=\"header\"><td class=\"first\">Rank</td><td class=\"second\">Name</td><td class=\"third\">Time</td><td class=\"fourth\">Speed</td></tr>\n";}
else {echo "";}
///// CHOOSE LIMITS TO SUIT TABLE LENGTH !!
if (($i>=26)&&($i<=50))
{echo "<tr class=\"".$rowcolourb."\"><td class=\"first\">".${"truerankfinal$i"}."</td><td class=\"second\">Person's Name goes here</td><td class=\"third\">".number_format(${"t$i"},3)."</td><td class=\"fourth\">XX.XXX</td></tr>\n";}
else if (($i>=50)&&($i<=51))
{echo "</tbody></table><!---End--Column2-------------------------------------------------------></td>\n\n\n";}
else {echo "";}
///// End of  Second table

///// Now do 3rd third of field
if ($i==51){
echo "<td class=\"noborder\"><!-----Column 3--------------------------------------------------->\n";
echo "<table class=\"threecolumns\"><tbody>";
echo "<tr class=\"header\"><td class=\"first\">Rank</td><td class=\"second\">Name</td><td class=\"third\">Time</td><td class=\"fourth\">Speed</td></tr>\n";}
else {echo "";}
///// CHOOSE LIMITS TO SUIT TABLE LENGTH !!
if (($i>=51)&&($i<=75))
{echo "<tr class=\"".$rowcoloura."\"><td class=\"first\">".${"truerankfinal$i"}."</td><td class=\"second\">Person's Name goes here</td><td class=\"third\">".number_format(${"t$i"},3)."</td><td class=\"fourth\">XX.XXX</td></tr>\n";}
else {echo "";}
///// End of Third table

++$i;}
?>
<!---------- END OF PHP RANKING CODE  --------->

</tbody></table><!---End--Column3-------------------------------------------------------></td></tr></tbody></table>
</center>
</body>
</html>

r937 · Nov 7, 2009

Code:

SELECT t.name
     , t.time
     , ( SELECT COUNT(*) + 1
           FROM test_rank
          WHERE time < t.time ) AS rank
  FROM test_rank AS t
ORDER
    BY t.time

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

maharg · Nov 8, 2009

Many thanks Rudy!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

True ranking in mySQL 1

maharg

Technical User

maharg

Technical User

maharg

Technical User

r937

Technical User

maharg

Technical User

Similar threads

Part and Inventory Search

Sponsor