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.
Query 1:
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:
Query 2:
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:
Query 3:
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)
63 Comments. Leave new
In Query 2 “ORDER BY 2” means order by second column but there is only one column, that’s why we got an error.
In Query 3 “ORDER BY ‘2’ ” means order by column named ‘2’ and it works.
On Query 2, I think it arises the error because the ORDER BY was looking at the second column but the UNION ALL only has one column. Lastly, on Query 3 it was working because you alias the column as ’2′ and order it by that column name.
for Query 1, order by 1 meaning it is order by first column. The result show correctly.
for Query 2, order by 2 meaning it is order by second column. The table didn’t have the second column, so there will be something ‘out of range’ error.
for Query 3, order by ‘2’ meaning it is order by column named ‘2’. The table declare column named ‘2’, so there will no error on scripts.
Interesting puzzle :)
Here is my understanding. Query 1 “order by 1” refers 1 as column order seq. and not column name. Query 2 order by considers “order by 2” as column order sequence. Since there is only one column is available here, returns error msg. But query 3 has the column alias “2”. Here “order by 2” refers column name.
hi pinal,
The main difference between query2 & query3 are ORDER BY with Column position which is not exist in the SELECT statement and ORDER BY with column name…..
in query2, the ORDER BY clause have the column position/index(i.e. 2 ).
query engine searches for 2nd column name(the column name which is at 2nd position) in the SELECT statement. That is the reason ERROR happened. Whereas query 3 has ORDER BY clause with column name…. so that column name exists in the SELECT statement
The second query try to order by column #2, column #2 doesn’t exist
Query 2 is trying to order the column with an ordinal position of 2. Query 3 is ordering by the column named ‘2’.
I’m reposting a more complete answer:
Query 2 is trying to order the column with an ordinal position of 2. Query 3 is ordering by the column named ’2′. Since a column in position 2 does not exist (there is only 1 column) the query returns an error. However, since there is a column named ‘2’ and the query is ordering by that name (‘2’), the query is successful since it exists.
The second query gives you an error because the order clause is referencing a column that doesn’t exist. Transact SQL syntax allows for integers to be used in an order by clause to reference columns 1, 2, 3, … etc. However, in the third query using the “AS” syntax gave the column a label that can be referenced by name in the order by clause.
Because in the second query the 2 is considered as column number and the select doesnt contain the second column. Thats why it gives error.
But in the third query 2 in single quotations is given as alias to the column. And so in the order by the 2 in single quotations is being considered as an alias of the column and not the column number.
In Query 2, order by 2 indicates sorting of result by second column, but query result contains only one column.
In Query 3, order by ‘2’ indicates sorting of result by column name ‘2’, which is there in query result, so it works.
Orderby statement works with Column Number or Column Name
Order by always have a reference of column name or ordinal of that column…in second query u giving order by 2 which mean second no of column…which is not present in query and in 3rd query 2 mark as column so its giving proper result…
if u run 2 query like below it would give u expected result
SELECT 2 from dual
union all
select 2 from dual
ORDER BY 1
in second query “order by 2” means sorting will be done on second column which is not available in the query. that’s why it is giving error. while in 3rd query sorting will be done one value 2.
ORDER BY 2 means order by second column,
>SELECT * FROM emp order by 2;
empno ename job mgr
7876 ADAMS CLERK 7788
7499 ALLEN SALESMAN 7698
7698 BLAKE MANAGER 7839
7782 CLARK MANAGER 7839
7902 FORD ANALYST 7566
7900 JAMES CLERK 7698
7566 JONES MANAGER 7839
& order by ‘2’ means order by column named/alias ‘2’
Reason is that 2nd query try to sort on column which is on second position (i.e. column index), but query returns only one column so 2nd column index does not found. While in 3rd query, column alias is used, so in 3rd query sorting is done by column name.
in 2nd Query we are saying order by second column; since there is no 2nd column in the selected list we got error.
In 3rd Query we are saying order by column name ‘2’. so it executed correctly.
Query 2 is ordering the column with an ordinal position of 2. Query 3 is ordering by the column named ’2′ by the alias
In the second query SELECT 2
UNION ALL
SELECT 2
ORDER BY 2
order by 2 indicates that we are sorting based on column2,but actually we dont have 2 columns,so it is giving error.
If u try like this then it works
SELECT 2,1
UNION ALL
SELECT 2,2
ORDER BY 2
Where as in 3rd query we are directly mentioning column name as ‘2’
If we change the query to
SELECT 2
UNION ALL
SELECT 2
ORDER BY 1
and use order by ‘1’ it give right result. Because Order By 2 means order by second column.