CODEWITHSUNDEEP
mysqlleft-join
user1372896
user1372896

Reputation: 534

Mysql Left outer join filter by

I'm trying to extract a leaderboard from data in a MySQL server. It's to show lap times on each map by certain players.

My current query I came up with so far is this:

select d1.*
from surf_times d1
left outer join surf_times d2
on (d1.Name = d2.Name and d1.Time > d2.Time)
where d2.Name is null
order by Time;

This returns the correct results however I need to filter it by map. An example table can be found at http://sqlfiddle.com/#!2/3e9c6/1

This query will respond with:

SteamID             Name                    Map             Time    Date
76561197991519598   Kuratheris              surf_utop       60.05   1445107360
76561198129490626   xXNightw0lfXx           surf_ace        60.84   1445106920
76561198156238243   ☆ The Pic ☆         surf_utop       62.35   1445107724
76561198049179442   J4N412N3                surf_utop       69.53   1445107519
76561197994977992   Rockape2620             surf_ace        72.26   1445107047

This is almost correct, however my query needs to only return the map selected rather than times from all maps. The correct query should respond with the top 15 times for the selected map for example "surf_utop" should respond with the following table:

SteamID             Name                    Map             Time    Date
76561197991519598   Kuratheris              surf_utop       60.05   1445107360
76561198156238243   ☆ The Pic ☆         surf_utop       62.35   1445107724
76561198049179442   J4N412N3                surf_utop       69.53   1445107519

I've had a look at other questions such as SQL Select only rows with Max Value on a Column however have not been able to figure it out.

Upvotes: 1

Views: 402

Answers (2)

Misa Lazovic
Misa Lazovic

Reputation: 2823

You don't need to JOIN the whole table again, you can use:

SELECT st.*
FROM surf_times st
WHERE st.Time = 
    (SELECT MIN(t.Time) 
    FROM surf_times t 
    WHERE t.SteamID = st.SteamID AND t.Map = st.Map) 
        AND st.Map = 'surf_utop' -- or any other map
GROUP BY st.SteamID
ORDER BY st.Time
LIMIT 15;

Upvotes: 1

John Ruddell
John Ruddell

Reputation: 25862

so just add on to your WHERE the selected map.

select d1.*
from surf_times d1
left outer join surf_times d2
on (d1.Name = d2.Name and d1.Time > d2.Time)
where d2.Name is null AND d1.map = 'surf_utop'
order by Time
limit 15;

fiddle example

result:

+-------------------+-----------------+-----------+-------+------------+
|      SteamID      |      Name       |    Map    | Time  |    Date    |
+-------------------+-----------------+-----------+-------+------------+
| 76561197991519598 | Kuratheris      | surf_utop | 60.05 | 1445107360 |
| 76561198156238243 | ☆ The Pic ☆ | surf_utop | 62.35 | 1445107724 |
| 76561198049179442 | J4N412N3        | surf_utop | 69.53 | 1445107519 |
+-------------------+-----------------+-----------+-------+------------+

Upvotes: 1

Related Questions