Custom ORDER BY Function SQL

Question

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

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

Copy Paste!
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.

Use a INLINE User Defined Function

McNets
– McNets

2017-03-09 22:40:59 +00:00
Commented Mar 9, 2017 at 22:40 — McNets
– McNets, Commented Mar 9, 2017 at 22:40

Gordon Linoff · Accepted Answer · 2017-03-09 22:45:28Z

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.

Jean-Bernard Pellerin · Accepted Answer · 2017-03-09 22:40:32Z

5

Create a second table with two columns (name, rank)

SELECT * 
FROM example e INNER JOIN table2 t2 on e.name = t2.name
ORDER BY t2.rank ASC;

And your second table will contain the pairings.

answered Mar 9, 2017 at 22:40

Jean-Bernard Pellerin

12.7k10 gold badges59 silver badges82 bronze badges

3 Comments

Gordon Linoff Over a year ago

I would discourage this approach because a JOIN can affect the semantics of the query.

Jean-Bernard Pellerin Over a year ago

Huh, I always just assumed the db would be smart enough to optimize. Thanks for commenting otherwise I never would have read your answer and learned this.

Gordon Linoff Over a year ago

The following query might not produce the same results: select e.*, count(*) over () from example.

Collectives™ on Stack Overflow

Custom ORDER BY Function SQL

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related