SQL Custom Order

I recently had a problem while creating a list of players for a project. I needed to list them in a certain order based on their position.

I knew there were only 4 possible positions, but wanted them returned in the order of GK, DEF, MID and STK.

I managed to find two ways of doing this which I will demonstrate below.

The first way was to use an Order by. The script below orders the table first by my Custom order, and then by the ID Number field.

The proper way to do this is in your table structure. Edit the field that is storing the value and change the type to an ENUM, and set its value to ‘GK’,’DEF’,’MID’,’STR’.

Then when you do your normal select statement, just order by strPosition (field name) and it will produce it in the order of your ENUM value.