Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    okey, i try the other way now:

    PHP Code:
    map    time

    map1    22.12
    map1    33.22
    map1    55.33
    map1    11.44
    map1    99.99
    map2    10.00
    map2    08.00
    map2    56.23
    map2    23.54
    map3    01.01
    map3    32.23
    map3    93.33
    map3    45.33
    map3    47.44
    map3    77.44 

    how get top 3 time each map, like this:

    Code:
    map1	11.44
    map1	22.12
    map1	33.22
    map2	08.00
    map2	10.00
    map2	23.54
    map3	01.01
    map3	32.23
    map3	45.33

  2. #17
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,869
    Thanks
    2
    Thanked 163 Times in 158 Posts
    Load the data into an associative array where the keys are the 'map' fields and their value is an array of the times. Then sort the array(s) and output only the first (or last) 3 elements of each array.

  3. #18
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How?

    Like that i get only first times:

    Code:
    SELECT a.* FROM records a INNER JOIN(SELECT map, MIN(time) min_time FROM records GROUP BY map) b ON a.map = b.map AND a.time = b.min_time order by map

    edit:
    This works well, but why it is so slow?:

    Code:
    SELECT * FROM records s WHERE(SELECT COUNT(*) FROM records f WHERE f.map = s.map AND f.time <= s.time) <= 3
    and one more thing, how i get
    Code:
    count(name)
    like:
    Player1 8
    Player2 6
    player3 3
    ....
    Last edited by Radeom; 11-07-2013 at 05:44 PM.

  4. #19
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,869
    Thanks
    2
    Thanked 163 Times in 158 Posts
    Use the EXPLAIN function to see how mysql executes the statement.

  5. #20
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, but how i get this name count? i try something:
    Code:
    SELECT map, time, count(name) FROM records s WHERE(SELECT COUNT(*) FROM records f WHERE f.map = s.map AND f.time <= s.time) <= 3
    but its wrong, it give me only one row and count show rows amount

    maybe i need make two querys?

  6. #21
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,869
    Thanks
    2
    Thanked 163 Times in 158 Posts
    Well, that's what the count() function does, it returns the number of rows. Did you expect it to do something else?

    It's not clear to me what you're trying to do with that sql statement.

    If we go back to your prior post where you wanted to get the top 3 times for each map, you could and it would be easier to calculate/extract that via the php code rather than trying to build a single sql statement. I seriously doubt that you'll be able to construct an sql statement that returns the data exactly the way you want.


 
Page 2 of 2 FirstFirst 12

Posting Permissions

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