Artur Hefczyc

 A few weeks ago I wrote an article about custom sorting order in SQL statement.

Today I tried to do the same in MS SQL Server. Obviously the solution described there didn't work as SQL Server has it's own ways to do things. It uses 'case' statement to define custom order. The query for SQL Server should look like this:

select Animal from animals 
order by case id
  when 3 then 1
  when 4 then 2
  when 10 then 3
  when 7 then 4
  when 9 then 5
  when 5 then 6
  when 6 then 7
  when 1 then 8
  when 2 then 9
  when 8 then 10
end;

The question which now arises is what happens if the 'id' is different from all possible values specified in the case statement? Well, the order for such a value is unspecified therefore the result is the same as if you specified else null after the last when. You can control it however and put there else 1000 after the last when. In such a case all cases not enumerated in the case will go to the end of the result lists and you can filter them out with top instruction:

select top 10 Animal from animals 
order by case id
  when 3 then 1
  when 4 then 2
  when 10 then 3
  when 7 then 4
  when 9 then 5
  when 5 then 6
  when 6 then 7
  when 1 then 8
  when 2 then 9
  when 8 then 10
  else 11
end;