case statements with mixed ordering in an order by

[Former Member]

I need to return a query in different ordering according to some criteria. Specifically, as an example:

• Sort Criteria 1: LastName Asc, FirstName Asc, LastVisit Desc
• Sort Criteria 2: FirstName Asc, FirstName Asc, LastVisit Desc
• Sort Criteria 3: LastVisit Desc, LastName Asc, FirstName Asc

Is there anyway of doing this without repeating the same select query 3 times, once for each sort criteria? It seems "CASE" does not work when you need to sort by more than one field and in different order.

[VolkerBarth]

Just to clarify: Do you have to return the same result set three times in different order or do you have to return one result set in one of three (or more) possible orders? - Based on your question, I think you are trying to achieve the former... And is the query itself (except the order) fixed so that you could use a view for that?

[Former Member]

You can rely on the optimizer cleaning things up for you and use a sequence of IFNULL/NULLIF as follows.

drop table if exists SortTest;
create table SortTest(
    pk int,
    FirstName varchar(32),
    LastName varchar(32),
    LastVisit timestamp
);
create index Criteria1 on SortTest(LastName Asc, FirstName Asc, LastVisit Desc);
create index Criteria2 on SortTest(FirstName Asc, LastName Asc, LastVisit Desc);
create index Criteria3 on SortTest(LastVisit Desc, LastName Asc, FirstName Asc);
insert into SortTest
select row_num, rand(), rand(), dateadd(day, rand() * 365, '2014-01-01')
from sa_rowgenerator(1,5000);
commit;

Then you can repeat the entire ORDER BY criteria:

select top 10 *, 3 as @criteria
from SortTest
order by 
-- Criteria 1:
  ifnull(nullif(@criteria,1),LastName) asc
, ifnull(nullif(@criteria,1),FirstName) asc
, ifnull(nullif(@criteria,1),LastVisit) desc
-- Criteria 2:
, ifnull(nullif(@criteria,2),FirstName) asc
, ifnull(nullif(@criteria,2),LastName) asc
, ifnull(nullif(@criteria,2),LastVisit) desc
-- Criteria 3:
, ifnull(nullif(@criteria,3),LastVisit) desc
, ifnull(nullif(@criteria,3),LastName) asc
, ifnull(nullif(@criteria,3),FirstName) asc
OPTIONS(FORCE OPTIMIZATION)

The ifnull/nullif turn to NULL for the criteria not selected and they are then eliminated. You can check that the plan uses the indexes created above. If you put the statement in a stored procedure, it is a good idea to include FORCE OPTIMIZATION to avoid plan caching -- it probably won't be a good idea.

You could use a variable in place of the alias @criteria I used for exposition.

[Former Member]

You can create your select in string and use execute immediate to run select inside string.