select Speed.mapid, max(trank), speed, ifnull(alias, 'Unregistered Player')
from Speed, Fastest_players, Cts_times
left join Id2alias
    on Fastest_players.idvalue = cryptokey
where Speed.mapid = Fastest_players.mapid 
    and Cts_times.mapid = Speed.mapid
    and tvalue != 0
group by Cts_times.mapid
order by count(trank) DESC;

-- if condition tvalue != 0 is not present
-- database will return that maps have 99 records
