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