0

I have a database that looks like the following:

+-------------------+------------+-----------+-------+------------+------+
| SteamID           | Name       | Map       | Time  | Date       | id   |
+-------------------+------------+-----------+-------+------------+------+
| 76561198059749096 | Bølle     | surf_mesa | 51.74 | 1454877544 | 3757 |
| 76561198141936247 | SlachtHuis | surf_mesa | 51.76 | 1454783151 | 2110 |
| 76561198059749096 | Bølle     | surf_mesa | 51.83 | 1454876964 | 3729 |
| 76561198065863390 | dLGHT      | surf_mesa | 51.98 | 1455017166 | 4988 |
| 76561198059749096 | Bølle     | surf_mesa | 51.98 | 1454877206 | 3741 |
| 76561198065863390 | dLGHT      | surf_mesa | 52.02 | 1455017046 | 4986 |
| 76561198141936247 | SlachtHuis | surf_mesa | 52.03 | 1454839146 | 2838 |
+-------------------+------------+-----------+-------+------------+------+

This has thousands of results in for each map (Note the table isn't already sorted by time), what I'm wanting to do is select a SteamID and find his fastest time for the selected map. This would then return the total position out of all the other times.

So for example, I want to get 76561198065863390's position or rank out of all other times for surf_mesa. This would return a table or something similar to:

+-----+-------------------+
| pos | SteamID           |
+-----+-------------------+
| 4   | 76561198065863390 |
+-----+-------------------+

I started messing around with queries and came up with this:

SELECT * FROM
(
    SELECT (@pos:=@pos+1) pos,Map,Time
    FROM `surf_times`
    WHERE `SteamID` = "76561198065863390"
    ORDER BY `Time` DESC
) `surf_times`
WHERE `Map` = "surf_mesa" LIMIT 1

Got myself confused in that query and seemed to get further away from the answer :)

1

This should work:

SELECT pos, SteamId
FROM
(
    SELECT (@pos := @pos+1) pos
        , Map, Time, Date, SteamID
    FROM `surf_times` S
        , (SELECT @pos := 0) p
    WHERE `Map` = "surf_mesa" 
    ORDER BY `Time` 
) `surf_times`
WHERE `SteamID` = "76561198065863390"
ORDER BY pos
LIMIT 1;

SQL Fiddle

  • Seems to be returning a 4517 and 4518, this map should only have 900~ results. The position of that steamid should also be 3~ thanks – user1372896 Feb 13 '16 at 16:19
  • what is 4517 and 4518? The query works with your sample. Perhaps it must filter on Map as well in the subquery (not clear from your question) fiddle as been added. final order by pos was missing. – Julien Vavasseur Feb 13 '16 at 16:39
  • The position is 4517 or 4518, it also now shows 4091. I'm not sure where it is getting that number from as there is only 980 results in total for surf_mesa and 8831 results in total with other maps. – user1372896 Feb 13 '16 at 16:43
  • for 76561198065863390, number 4 is 51.98 for time and number 6 is 52.02 when order by time – Julien Vavasseur Feb 13 '16 at 16:48
  • That seemed to work, was my example incorrect? Thanks for your time – user1372896 Feb 13 '16 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.