SQL Ninja uses ORDER BY with CASE

SqlNinja Hi, today I want to show you simple technique which can be used when you need parameterised ORDER BY cause in T-SQL. Sometimes you may want to have elegant query. Example is from my today’s code review that I gave to colleague. On the Internet is very difficult to find such solution. So, I decided to show it to you here. There are many possible scenarios for this example usage. For example you may want to have in stored procedure nice simple query that depend on sort order parameter. Another example when you have to make it is use CTE that can be complicated with several queries flow. With this technique your T-SQL code will be much more compacted and may run faster. For sure it will be much simpler without separated queries for each ORDER BY. Enjoy!

use tempdb
go

-- prepare test context
if object_id('OrderByTest') is not null
  drop table OrderByTest
  
create table OrderByTest (
 Val1 VARCHAR(10),
 Val2 INT
)

insert into OrderByTest (Val1, Val2) values ('a', 2)
insert into OrderByTest (Val1, Val2) values ('b', 1)

-- I need those sort orders in 0 and 1 cases
-- 0
select * from OrderByTest order by Val1 ASC, Val2 DESC
-- 1
select * from OrderByTest order by Val1 DESC, Val2 DESC

declare @sort bit

set @sort = 0
select *
-- query with @sort param in order by
from OrderByTest
order by
    case when @sort = 0 then Val1 end ASC,
    case when @sort = 0 then Val2 end DESC,
    case when @sort = 1 then Val1 end DESC,
    case when @sort = 1 then Val2 end DESC

set @sort = 1
-- query with @sort param in order by
select *
from OrderByTest
order by
    case when @sort = 0 then Val1 end ASC,
    case when @sort = 0 then Val2 end DESC,
    case when @sort = 1 then Val1 end DESC,
    case when @sort = 1 then Val2 end DESC

-- clean up
if object_id('OrderByTest') is not null
  drop table OrderByTest

p ;).

2 Replies to “SQL Ninja uses ORDER BY with CASE”

  1. Hi Piotr!

    It is very tricky technique, however I use it primarily in WHERE clause to filter data depending on whether user has delivered given value or not. I don’t know why so few developers use it while building T-SQL queries…

    BTW. Cool blog, keep going!

    Regard,
    Maciek

  2. Hi Maciek,

    The longer story of this is fetching rows into variable table and then there were 2 almost identical queries divided by if statement to select top 1 row depending on the order by. Thanks to this technique I advice my colleague was able to create this as CTE and get only once top 1 row with order by regulation from outside procedure parameter. And performance is much better because CTE with top 1 works much better and we could eliminate variable table as well. That gives 0 logical writes.

    I like work on this blog, even with difficulties at the begin. I am happy you like it. I will keep going develop on this site for sure.

    Thanks!
    Piotr

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.