SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query syntax help - Query lowest result value per user

    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>
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  2. #2
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by chuckylefrek View Post
    ...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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    $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

    Code:
    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

    Code:
    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
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    So I need to amend my query so that echo $row['scoreTime'] displays what $row['f1'] is displaying
    Maybe like this?
    Code:
    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'] ?

  6. #6
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    $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
    ";
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  7. #7
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Maybe like this?
    Code:
    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:

    Code:
    $mysql_result = mysql_query($sql, $link);
    	$rs = array();
    	 while($rs[] = mysql_fetch_assoc($mysql_result)) 
    	 {
    	 }
    	 $false = array_pop($rs);
    	 
    	 
    	echo json_encode($rs);
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  8. #8
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Maybe like this?
    Code:
    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
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •