|
I need to return a query in different ordering according to some criteria. Specifically, as an example:
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. |
|
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. |
|
You can create your select in string and use execute immediate to run select inside string. |
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?