Query syntax help - Query lowest result value per user

[chuckylefrek]

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";

</code>

[chuckylefrek]

Update:

I have figured what is required is that the results use the value of f1 instead of scoreTime but not sure how this is done. Any help appreciated.

[r937]

...the results use the value of f1 instead of scoreTime but not sure how this is done.

not sure what you're saying here

note: any time you use the dreaded, evil "select star" together with GROUP BY, you're gonna have a bad time

[chuckylefrek]

Thank you for the reply Rudy.

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

while ($row = mysql_fetch_array($mysql_result)) {

		
		echo $row['fname'];
		echo $row['lname'];
		echo $row['scoreTime'];
		echo "<br />";

	}

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

If I use the following

while ($row = mysql_fetch_array($mysql_result)) {

		
		echo $row['fname'];
		echo $row['lname'];
		echo $row['f1'];
		echo "<br />";

	}

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

Really would appreciate any help with this

[guido2004]

So I need to amend my query so that echo $row['scoreTime'] displays what $row['f1'] is displaying

Maybe like this?

SELECT 
    fname
  , lname
  , fbname
  , MIN(CAST(scoreTime AS DECIMAL( 30, 2 ))) AS scoreTime
FROM score_t 
GROUP BY fbname 
ORDER BY scoreTime 
LIMIT 0 , 100

Although I don't see why you couldn't change your code to use $row['f1'] instead of $row['scoreTime'] ?

[chuckylefrek]

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
";

[chuckylefrek]

Maybe like this?

SELECT 
    fname
  , lname
  , fbname
  , MIN(CAST(scoreTime AS DECIMAL( 30, 2 ))) AS scoreTime
FROM score_t 
GROUP BY fbname 
ORDER BY scoreTime 
LIMIT 0 , 100

Although I don't see why you couldn't change your code to use $row['f1'] instead of $row['scoreTime'] ?

The reason I can't change my code is that it sends the results as follows:

$mysql_result = mysql_query($sql, $link);
	$rs = array();
	 while($rs[] = mysql_fetch_assoc($mysql_result)) 
	 {
	 }
	 $false = array_pop($rs);
	 
	 
	echo json_encode($rs);

[chuckylefrek]

Maybe like this?

SELECT 
    fname
  , lname
  , fbname
  , MIN(CAST(scoreTime AS DECIMAL( 30, 2 ))) AS scoreTime
FROM score_t 
GROUP BY fbname 
ORDER BY scoreTime 
LIMIT 0 , 100

Although I don't see why you couldn't change your code to use $row['f1'] instead of $row['scoreTime'] ?

Thank you very much guido2004 - your code seems to work perfectly.

Many thanks