It has been a long time since played a simple game on SQLAuthority.com. Let us play a simple game today. It is very simple puzzle but indeed a fun one.
First let us execute following SQL.
SELECT 1 UNION ALL SELECT 1 ORDER BY 1
It will return following result:
Now try to execute the following query and guess the result:
SELECT 2 UNION ALL SELECT 2 ORDER BY 2
When you execute the same it gives error that:
Msg 108, Level 16, State 1, Line 4
The ORDER BY position number 2 is out of range of the number of items in the select list.
Msg 104, Level 16, State 1, Line 4
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
Now let us execute following query and guess the result:
SELECT 2 AS '2' UNION ALL SELECT 2 AS '2' ORDER BY '2'
Above query will return following result:
Here is the question back to you – Why does a Query 2 returns error but Query 3 returns result successfully?
Just leave a comment with the answer – I will post the answer with due credit in future blog posts.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Oder by is working for sort by column or column sequence and the priority is
1- Column name then
Here in query 2 there is no column having name 2 but in 3 column having name hence no error