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.

asked 01 Jun '10, 11:11

Sergio's gravatar image

Sergio
460151820
accept rate: 0%

edited 22 Mar '14, 20:38

Graeme%20Perrow's gravatar image

Graeme Perrow
8.7k374112

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?

(01 Jun '10, 15:15) Volker Barth

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.

permanent link

answered 22 Mar '14, 18:53

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

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

permanent link

answered 01 Jun '10, 11:26

Zote's gravatar image

Zote
1.7k364050
accept rate: 43%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×3

question asked: 01 Jun '10, 11:11

question was seen: 3,460 times

last updated: 22 Mar '14, 20:38