Mysql Left outer join filter by

Question

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.

John Ruddell · Answer 1 · 2015-10-17T22:30:29.330

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 |
+-------------------+-----------------+-----------+-------+------------+

Misa Lazovic · Accepted Answer · 2015-10-17T22:42:20.320

1

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;

edited Oct 17 '15 at 22:42

answered Oct 17 '15 at 22:01

Misa Lazovic

2,805
10
32
38

this is bad advice. a dependent subquery is much worse than joining the table again. you should look up what this is doing – John Ruddell Oct 17 '15 at 22:10
This seems unlikely, but maybe I've misunderstood the question – Strawberry Oct 17 '15 at 22:10
JOIN is pretty memory expensive. It basically loads whole tables and joins them before "slicing them" using rules defined in WHERE clause – Misa Lazovic Oct 17 '15 at 22:13
no. JOIN is much less memory expensive than a correlated subquery. that subquery will execute once for each row in the table. JOINs are always faster than that and much less memory intensive. try running them yourself and you will see – John Ruddell Oct 17 '15 at 22:19
My table I'm querying is around 5000 lines long. For some odd reason this is only returning some results and not all of them? When searching for another map, no results get returned yet looking through the database there are 5 results with that map name. – user1372896 Oct 17 '15 at 22:23
This query will only return 15 lowest times for surf_utop map. What's the problem? – Misa Lazovic Oct 17 '15 at 22:24
Getting rid of the LIMIT part, it will only return 23 or so, tho I know for a fact the table I have has over 40 people who have completed this map. Some map's return only 2-3 yet they have 20+ people finished them? – user1372896 Oct 17 '15 at 22:25
Please check the data: Do IDs match? Is map name for those cases always the same? – Misa Lazovic Oct 17 '15 at 22:30
Yes, for example searching for the map "surf_lullaby_ksf" (SELECT * FROM `surf_times` WHERE `Map` LIKE 'surf_lullaby_ksf') in the table returns these: http://i.imgur.com/qEiIdrG.png but running this query: http://pastebin.com/hzCUMKML returns nothing. – user1372896 Oct 17 '15 at 22:33
You're missing ` AND t.Map = st.Map` in brackets – Misa Lazovic Oct 17 '15 at 22:34
1

Ah, my bad. That works perfect :). The only issue is someone having two of the exact same times will show up twice, but it's not really bad. Thanks! – user1372896 Oct 17 '15 at 22:38
1

I edited the query, added GROUP BY st.SteamID to avoid duplicates – Misa Lazovic Oct 17 '15 at 22:43

Mysql Left outer join filter by

2 Answers2