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
Order By works with Column Name or Column Alias and position of column, Query-1 works fine because There is only 1 column and it’s order by column position-1. Query-2 trying to Order with position-2 but there is only one column that’s why error occurred out of range. Query-3 working fine because it’s order by with column Alias..
:)
The ORDER BY thinks you are talking about the column number 2 if you don’t name it the string ‘2’. Too easy ;)
Order by clause works by column name or order of column ie position
Query 1 : sucessful because only one col ..
Query 2: result set don’t have another column so failed
Query 3 :resultset have column name and here order by on column name not by col position so i will work
second query is failing because order by is attempting to sort by second column which doesn’t exists and resulting in the error
third query works because of column aliasing
Hi,
1 – ok
2 – Position of column in order by clause is wrong
3 – The logical order of the query attend this new column.
Nice puzzle :)
Its because there is NO 2nd column as here Order By ColumnNumber used…
since we are selecting/sorting the second coloumn in the ORDER BY clause which is not there in SELECT clause.Therefore we are getting error in the second query.Instead of the second query
“select 2
union all
select 2
order by 2″
we can go for the following query which gives expected output
”
select 2
union all
select 2
order by 2″
since we are selecting/sorting the second coloumn in the ORDER BY clause which is not there in SELECT clause.Therefore we are getting error in the second query.Instead of the second query
“select 2
union all
select 2
order by 2″
we can go for the following query which gives expected output
”
select 2
union all
select 2
order by 1″
in query2, the ORDER BY clause have the column position (i.e. 2 ) which not exist in table where as in query 3 the order by with column name like ORDER BY ‘2’ which exist in select statement. this is the main difference.
Query 2 returns the error because any integer after order by cluse is considered as serial no. of the field in select list whereas in query 3; ‘2’ is a string, considered as alias of column in select list which is present and query runs successfully.
First query will run smoothly, because there is only 1 column in the select statement and we want result set by that column.
Second query will give error because there is only 1 column in the select statement and we want result set by second column which doesn’t exist in the select statement.
Third query will also run smoothly, because we have alias the column name in select statement and we want result set by that alias name.
Query 1:
It is selecting the value (integer 1) in first part (No column aliasing done). Again selects value (integer 1) in second part (No column aliasing done). Now, Union is performed with the single column having name not assigned (as first part is not giving any name hence no name for column in Union’s result as well) . Next, it is sorting the result with the column at ordinal position 1(but not using the column name). And hence it is giving the output after sorting as per column at position 1. Hence no error and output is without column name.
Query 2:
Here, the entire concept is same like Query 1 except the “ORDER BY” part. So it tries to sort the rows as per column at ordinal position 2. There is no column at position 2. Hence, the mentioned errors.
Query 3:
Here also we have everything similar to Query 1 except the “Aliasing” Concept. The first part’s column alias(‘2’) makes the column name of the Result of Union All as 2. And now when it is sorting the result, there also it is sorting with the column named 2 and not the ordinal position 2. Hence the query is a success.
Query 2- This query has a clause order by 2, which means order by position 2 (order by the second column of the output) and the output itself has only one column. As a result the query returns an error.
Query 3 – This query’s output column itself has a name ‘2’ and the order by clause is using this name ‘2’ and hence it works.
The second query fails because you are trying to order by a second column, which doesn’t exist, there is only one column in the result set.
The third query succeeded because you are ordering by a column name, 2, and not a column position.
Query 2 : it searches for the field in 2 position of select query for ordering the result so the 2nd field value not available.
Query 3 : It has mentioned column name in order by clause which is present in query.
The Query 2 uses the ORDER BY clause having expression based on ordinal position of the column in select statement. Where as only one column is selected in the select statement so the column number 2 is out of range, resulting in the query returns an error.
The Query 3 uses the column name (alias name) in the ORDER BY clause expression, which is the valid name as in the select statement thus executing the query successfully.
ORDER BY clause is trying to sort the second column which is not available in the querey
SELECT 2
UNION ALL
SELECT 2
ORDER BY 1
SELECT 2
UNION ALL
SELECT 2
ORDER BY 2
this query is invalid, because there is no position number 2 in the SELECT list. For both queries an ORDER BY 1 would make the statements valid and executable.
ANS:-
SELECT 2
UNION ALL
SELECT 2
ORDER BY 1