How to do variable/conditional SQL sorting order of results

I was jazzed today. I figured out how to conditional order of SQL results. Specifically being able to vary change the sorting order within the results based on a database field.

Specifically,

1. Create two tables Main and Secondary
   • create table Main ( ID bigint, ordering varchar(3));
   • create table Secondary ( Main_ID bigint, Secondary_Value bigint);
2. Insert Values:
   • insert into Main (ID, ordering) values (1, 'u'),(2,'d'),(3,'n');
   • insert into Secondary (Main_ID, Secondary_Value) values ( 1, 11),(1,21),(1,41),(1,31),(1,71),(1,61),(1,51),(2,62),(2,42),(2,82), ( 3,3), (3, 1), (3, 5);
3. Create a query that orders the results by Main.ID, Secondary.Secondary_Value based on Main.ordering value. If the Main.ordering is:
   • ‘u’ then the corresponding Secondary_Value is ordered ascending,
   • ‘d’ then the corresponding Secondary_Value is ordered descending,
   • ‘n’ then the corresponding Secondary_Value is unordered

The solution is:

select * from Main join Secondary on ( Main.ID = Secondary.Main_ID ) order by Main.ID, (case MAIN.ordering when 'u' then 1 when 'd' then -1 else 0 end) * Secondary.Secondary_Value;

+------+----------+---------+-----------------+
| ID   | ordering | Main_ID | Secondary_Value |
+------+----------+---------+-----------------+
|    1 | u        |       1 |              11 | 
|    1 | u        |       1 |              21 | 
|    1 | u        |       1 |              31 | 
|    1 | u        |       1 |              41 | 
|    1 | u        |       1 |              51 | 
|    1 | u        |       1 |              61 | 
|    1 | u        |       1 |              71 | 
|    2 | d        |       2 |              82 | 
|    2 | d        |       2 |              62 | 
|    2 | d        |       2 |              42 | 
|    3 | n        |       3 |               1 | 
|    3 | n        |       3 |               5 | 
|    3 | n        |       3 |               3 | 
+------+----------+---------+-----------------+
13 rows in set (0.00 sec)

Variable ordering within a single result set! woo-hoo!