I have a database table that contains a varchar field that stores the times players have taken to complete a game. The times are effectively decimals but are stored in the database as varchars. I was given this populated database by my client.
I need to query the database to pull out a list of the top 100 lowest times however I do not want more than one result for any user.
Here is the code that I have tried but it is not working. What it appears to be doing is creating a list of the users who scored the lowest scores in the correct order but not displaying their lowest score.
Note the field fbname is a unique facebook id which identifies unique users.
Can anyone point me in the right direction please
<code>
$sql = “SELECT *, MIN(CAST(scoreTime AS DECIMAL( 30, 2 ))) AS f1 FROM score_t GROUP BY fbname ORDER BY f1 LIMIT 0 , 100”;
It appears that the result set includes the wrong value for scoreTime but the correct fbname.
I could amend my code to remove the evil “select star” as follows:
$sql = "SELECT fname, lname, fbname, MIN(CAST(scoreTime AS DECIMAL( 30, 2 ))) AS f1 FROM score_t GROUP BY fbname ORDER BY f1 LIMIT 0 , 100";
My query seems to pull out the correct order in terms of who got the lowest scores but it does not pull out the lowest score for each of these users. So whatever value of scoreTime is being returned in the results is wrong for some reason.
So basically if I use the following to trace the results
I am getting the list of names in the correct order i.e the first name on the list is the person who had the lowest score however the score displayed is not their lowest score
It does display the correct lowest score for this user. So I need to amend my query so that echo $row[‘scoreTime’] displays what $row[‘f1’] is displaying
It may be helpful to see the code that was given to me by the other developer however this code appeared to display multiple results from the same user
$sql = "SELECT SQL_NO_CACHE * , CAST( scoreTime AS DECIMAL( 30, 2 ) ) AS f1
FROM score_t t
WHERE NOT EXISTS ( SELECT NULL FROM score_t h
WHERE h.fbname = t.fbname
AND CAST( h.scoreTime AS DECIMAL( 30, 2 ) ) < CAST( t.scoreTime AS DECIMAL( 30, 2 ) )
)
ORDER BY f1,lname
LIMIT 0 , 999
";