Wednesday, August 14, 2019

Order by in subqueries

This SQL Server behaviour is irritating me a lot.

Again, maybe it's according to the ANSI SQL standard (don't know) but even if it is, it's not making my irritation go away.
What am I talking about?

Using order by in subqueries and views...

There's absolutely not a problem to use order by in subquery. Not at all.
select * from (
    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
    )
    order by x
) order by x desc
         X
----------
         4
         3
         2
         1


If we try doing the same in SQL Server we get this:
select * from (
    select * from (
        select 2 as x union all
        select 4 as x union all
        select 1 as x union all
        select 3 as x
    ) a
    order by x
) b order by x desc

Msg 1033, Level 15, State 1, Line 41
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Why? Can't the database engine simply ignore this clause or move it to top query? Or maybe.... execute? Ignore strategy would be good enough...

Some may say 'What's the problem? You can comment it out'. But I say that these people never did any serious work in SQL. If you work extensively with SQL then how many times have you been caught seeing this error message when playing around with queries? When you start building query bottom up, have some data, analyse it and say to yourself 'I need to make a subquery/view out of it' and then you have to comment out those parts or remove... And then, when debugging some time later you have to uncomment or add new order-bys looking for errors.

It's such a stupid behaviour...

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