Dynamic "ORDER BY" based on data in database

SQL Apprentice Question
I'm dealing with a legacy system (yecch) that I'm trying to search. The data
I'm searching is a flattened representation of a tree. My problem is that my
search results aren't being displayed in the correct order.
Sorting order is controlled by settings stored in the database in two
different locations for each level of the tree. If a particular level's
search value is 0, sorting is controlled by a value in the root. Otherwise,
the value in the node is used. The search value is an integer from 1 to 12,
each number identifying the column or columns that the data will be sorted
against.

In a perfect world, I could have a query that looked something like:


declare @bSortBy varchar(20) -- holds the text to ORDER BY
switch (bOrder == 0 ? aOrder : bOrder) -- use b's sorting value only if not
zero
case 1:
select @bSortBy = "b.name" -- sort by node name
break
case 2:
select @bSortBy = "b.dateCreated" -- sort by node creation date
break
-- do the same for tables c and d
SELECT aId, bId, cId, dId WHERE foo = bar ORDER BY @bSortBy, @cSortBy,
@dSortBy


I guess the big problem I have is ordering by different fields depending on
the data I get from my search hits. Is there any way to do this in Sql???
Any pointers would be appreciated.



Celko Answers
>> The data I'm searching is a flattened representation of a tree. <<


Get a copy of TREES & HIERARCHIES IN SQL for some other ways to model
this kind of data. That is a better long-term answer.

Standard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it can
only see the column names that appear in the SELECT clause list that
was used to build the result set. BP will now chime in that SQL-99
(officially called "a standard in progress" and not recognized by the
U.S. Government for actual use) does allow this.


But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.


The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per column:


SELECT
CASE @flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
...
CASE @flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,


FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...


More than one sort column and only a limited set of combinations then
use concatenation.


CASE @flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,


If you need ASC and DESC options, then use a combination of CASE and
ORDER BY


CASE @flag_1
WHEN @flag_1 = 'a' AND @flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @flag_1 = 'b' AND @flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @flag_1 = 'c' AND @flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @flag_1
WHEN @flag_1 = 'a' AND @flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @flag_1 = 'b' AND @flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @flag_1 = 'c' AND @flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d


.. ORDER BY sort_1_a ASC, sort_1_d DESC


I have shown explicit CAST( AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.


You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.


A neater way of doing this is to use one column for each sorting option
so you do not have worry about CAST() operations.


SELECT ...
CASE WHEN @flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;