Custom ORDER BY Function SQL

Question

I have requirements for a custom ORDER BY clause that looks much like this

SQL Custom Order By Clause

SELECT * FROM example
ORDER BY 
CASE 
    WHEN name = 'I want this first' THEN 0
    WHEN name = 'I want this second' THEN 1
    WHEN name = 'We get the picture' THEN 2
    ELSE 99   END ASC

However, this case statement has grown and I want to be able to reuse the sort order for other queries.

I see my options being

1. Copy Paste!
2. Adding another column to the table specifically for this sort order

In my head, it seems like I should be able to pass a function to perform the sorting logic. But after some searching on SO and google I couldn't find anything and thought this might also help someone else down the road.

Answer 1

One option is a CTE:

with ordering as (
      select v.*
      from (values(1, 'val1'), (2, 'val2'), (3, 'val3')) v(priority, val)
    )
select e.*
from example e
order by (select priority from ordering o where o.val = e.name);

I would discourage you from doing an explicit join, because that could affect the semantics of the query. A join filters the records before the select, where, group by, and having clauses are processed (logically). The order by only uses results in the result set (logically).

You can, of course, store the CTE values in a temporary table or table variable. That might be what you are after.

Another approach is a bit of a hack, but works under some circumstances. You can replace the logic with:

order by charindex('[' + name + ']', '[name1][name2][name]')

This assumes that all names are in the list. And that names don't have the delimiter characters. This doesn't directly solve your problem, but you can store the string as a variable in a code block or even as a computed column in a table.