MySql - order a query result in "some mode"

Question

Hello. I have this data on a table :

id  name        field
0   marco       attack
1   andrea      defense
2   luca        medium
3   ernesto     defense
4   vittorio    medium
5   manuele     attack

i need to order as field. BUT, the priority list order (for my example) should be defense-medium-attack.

so it must return :

andrea, ernesto, luca, vittorio, marco, manuele.

How can do it? bye

Answer 1

SELECT
       X.id,
       X.name,
       X.field
FROM (
      SELECT id, 
             name, 
             field, 
             CASE field WHEN 'defense' THEN 1
                        WHEN 'medium'  THEN 2
                        WHEN 'attack'  THEN 3
             END AS SortValue
      FROM MyTable) AS X
ORDER BY X.SortValue

Answer 2

You should store the fields in a separate table and give them a sort order. Then you can join to that table.

As well as allowing you to sort efficiently, it also makes the table structure more relational - which is good.

id field    sort
1  defense  1
2  medium   2
3  attack   3

id  name        field
0   marco       3
1   andrea      1
2   luca        2
3   ernesto     1
4   vittorio    2
5   manuele     3


select p.name, 
    ps.field
from players p
join playersort ps
    on p.field = ps.id
order by ps.sort