Kenneth Fisher shows various methods for conditional ordering:
Simple sorts are pretty easy. In the ORDER BY clause you can list out the columns by name or by position in the field list and determine if you want them sorted ascending or descending. But how about going beyond that?
Every now and again you need a fairly complex sort order. Say for example you want to order your data differently depending on the Status (say DueDate if theStatus is 1, 2 or 3 and ShipDate otherwise). But you also want anyone with aTotalDue greater than $5000 sorted separately from those with a TotalDue less than $5000.
Conditional sorting isn’t something you should be doing in every query, but it’s important enough to know about.