codice:
select *,
case 
when ruolo = "portiere" then 1
when ruolo = "difensore" then 2
when ruolo = "centrocampista" then 3
when ruolo = "attaccante" then 4
else 5
end as ordine
from tabella
order by ordine,cognome
Else 5 è l'ipotesi per l'allenatore. Adattala al tuo caso specifico.