Friday, August 30, 2019

Duplicate columns in order by

Another hard-to-understand anti-feature of MS SQL Server presents itself in an order by clause. SQL Server actively disallows a single column to be present multiple times in an order by.
Why, why, why?
Why can't you just ignore all but first occurence of a column?

select * 
from (
    select 2 as x union all
    select 4 as x union all
    select 1 as x union all
    select 3 as x
) dataset
order by x, x;
Msg 169, Level 15, State 1, Line 105
A column has been specified more than once in the order by list. Columns in the order by list must be unique.


Oracle does not care about it:
select * from (
    select 2 as x from dual union all
    select 4 as x from dual union all
    select 1 as x from dual union all
    select 3 as x from dual
) dataset
order by x, x;
         X
----------
         1
         2
         3
         4


Again, I expect to hear, it's not a problem to delete/comment out part of your code. It's not, but it's very often to move columns around when prototyping, especially, temporary moving column from back of the list to beginning. Normally I'd copy the column name, put it in front, run and delete. With MS SQL you have to do more steps and still, sooner or later you miss one or two places and see above mentioned error. If you add this to other MS SQL Server order by issues... eh...

Best.

No comments:

Post a Comment

Non fractional division (int/float)

For me a RDBMS is a high level tool. Something very opposite to using low level languages like C, C++ or... Assembler. From user/developer p...