order by custom criteria

[hardanro]

I hava a database with soccer players, and in one field it's stored the position of the player 'GK','TK','MD','SH'. One some pages i need to show these players in the order of their position Gk firsts then TK MD and SH. How can i do this using an order by clause, how to tell mysql to look first for GK in the position then TK MD AND SH without using more queries ore another dummy field to order by it (a dummy field means i put 0 in for GK, 1 for TK 2 for MD and 3 for SH and order by this field).

[ultraslacker]

Use CASE in the ORDER BY clause -

ORDER BY CASE field_position WHEN 'GK' THEN 0 WHEN 'TK' THEN 2 ... END