Arbitrary SQL order by

Posted on October 4th, 2008 by Dalton Filho

Consider the following scenario: the business analyst of your company sends you a business requirement in which the text results of a given query must be ordered arbitrarily according to the specifics of that business requirement. For example: Us should precede Cs, which should precede Uns, which should precede Bs, which should precede everything else. It can be very costly to resort to manipulations of the result set after the query has been executed, while a stored procedure is not very portable. You can make the query itself return the results in the arbitrary order you wish by modifying the select statement in such a way that your arbitrary order will output a number (or any other orderable output) that can be used as an order by condition.