Custom Sorting in SQL order by clause?

Question

Here is the situation that I am trying to solve:

I have a query that could return a set of records. The field being sorted by could have a number of different values - for the sake of this question we will say that the value could be A, B, C, D, E or Z

Now depending on the results of the query, the sorting needs to behave as follows: If only A-E records are found then sorting them "naturally" is okay. But if a Z record is in the results, then it needs to be the first result in the query, but the rest of the records should be in "natural" sort order.

For instance, if A C D are found, then the result should be

A
C
D

But if A B D E Z are found then the result should be sorted:

Z
A
B
D
E

Currently, the query looks like:

SELECT NAME, SOME_OTHER_FIELDS FROM TABLE ORDER BY NAME

I know I can code a sort function to do what I want, but because of how I am using the results, I can't seem to use because the results are being handled by a third party library, to which I am just passing the SQL query. It is then processing the results, and there seems to be no hooks for me to sort the results and just pass the results to the library. It needs to do the SQL query itself, and I have no access to the source code of the library.

So for all of you SQL gurus out there, can you provide a query for me that will do what I want?

Answer 1

How do you identify the Z record? What sets it apart? Once you understand that, add it to your ORDER BY clause.

SELECT name, *
FROM [table]
WHERE (x)
ORDER BY
    (
     CASE
       WHEN (record matches Z) THEN 0
       ELSE 1
     END
    ),
    name

This way, only the Z record will match the first ordering, and all other records will be sorted by the second-order sort (name). You can exclude the second-order sort if you really don't need it.

For example, if Z is the character string 'Bob', then your query might be:

SELECT name, *
FROM [table]
WHERE (x)
ORDER BY
    (
     CASE
       WHEN name='Bob' THEN 0
       ELSE 1
     END
    ), name

My examples are for T-SQL, since you haven't mentioned which database you're using.

Answer 2

There are a number of ways to solve this problem and the best solution depends on a number of factors that you don't discuss such as the nature of those A..Z values and what database product you're using.

If you have only a single value that has to sort on top, you can ORDER BY an expression that maps that value to the lowest possible sort value (with CASE or IIF or IFEQ, depending on your database).

If you have several different special sort values you could ORDER BY a more complicated expression or you could UNION together several SELECTs, with one SELECT for the default sorts and an extra SELECT for each special value. The SELECTs would include a sort column.

Finally, if you have quite a few values you can put the sort values into a separate table and JOIN that table into your query.