Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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

accept rate: 0%

edited 22 Mar '14, 20:38

Graeme%20Perrow's gravatar image

Graeme Perrow

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);

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

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
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

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 01 Jun '10, 11:11

question was seen: 4,558 times

last updated: 22 Mar '14, 20:38