SQL - Beyond the standard ORDER BY ASC / DESC

Recently at work a request came in from a user to update an existing report so it applied a specific order to the reported data. Previously the report ordered items alphabetically by Class and then Model, however, this was no longer suitable and a non-alphabetical ordering was required. 
Initially when I read this requirement I thought “Oh heck, how am I going to do this as it’s no longer in alphabetical order?”. As is often the case, the solution was simpler than expected.
I have created a simplified representation of the data in use to illustrate the technique I used to accomplish the custom sort order below.

User Requirement:
Order products in the following Class order
R-Class
ZA-Class
T-Class
G-Class
J-Class

As discussed earlier, this is not in alphabetical order so the following SQL statement will not produce the required result:

SELECT Model, Class
FROM Products
ORDER BY Class, Model

As we need to apply a specific order, we can accomplish this by introducing an additional numeric field into the result set which is solely used for ordering the results. This additional field can be produced by using a CASE WHEN statement to determine the correct numeric value to ensure the correct ordering is applied.

SELECT Model
 , Class
 , CASE
  WHEN Class = 'R-Class' THEN 1
  WHEN Class = 'ZA-Class' THEN 2
  WHEN Class = 'T-Class' THEN 3
  WHEN Class = 'G-Class' THEN 4 
  WHEN Class = 'J-Class' THEN 5      
 END AS ProductClassOrdering
FROM Products
ORDER BY ProductClassOrdering, Model

The ProductClassOrdering field will contain a numeric value which can be used as part of the ORDER BY clause to ensure the users custom ordering is applied.

Boom! This technique creates the desired result but it also introduces the use of an interesting technique: creating a field dynamically as part of your result set. It can be very easy to believe a SELECT statement can only select static data from a table (I will admit I was guilty of this belief when I first learnt SQL) but this is not true. If you start viewing each select value as a piece of data which can be generated by other means within the SQL languages constraints, you will find a whole new range of querying options available to you (sub-selects, logic to determine a value, additional filtering to name a few). 

However, a word of warning is needed. With these additional SQL powers comes great responsibility which can be summarized in a single word: performance. Keep one eye on the performance of your query when introducing additional SQL statements into the SELECT portion of your query as you can inadvertently introduce performance bottle necks into your query. 

Have a play and start flexing those SQL muscles a little more.