Mysql – Get Position of row from query

MySQL

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 🙂

Best Answer

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

Related Question