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
Easy question ;).
1st query: We order by the first column (1)
2nd query: We try to order by the second column (2) wich obviously does not exists
3rd query: We gave an alias to the first column of [2]. So we can order by the column named “2”.
In Query 2 :- “ORDER BY 2″ means sort data on second column but there is only one column, so its given an error.
In Query 3 “ORDER BY ’2′ ” means sort data by column named ’2′ and this query execute successfully.
In the second query Order by clause gets Column position of result which is not exists in result set.
hai pinal sir ,
its very interesting article … as per my knowledge i think like this
in second query the order by 2 indicates ,it orders the column 2. but here we dont have column 2 thats y it gives error
coming to 3 query here we used alias names and here the alias name is “2” that is the column name so here we ordered the column 2 so it didnt give any error am i correct pinal sir
Order by 1 stands for column number,
In query 1 ,only one column available so order by 1 work well.but in query 2 there is no second column,it shows error. In query 3 we set alias name for column, so it didn’t show any error.
Order by work with column number, specified field name and column mentioned alias name.
Orderby statement works with Column Number or Column Name and in second query order by 2 denoting to second column which is not there that’s why the 2nd query is throwing an error
IN 3rd query it’s order by column Name therefore no error
In the 2nd Query order by 2 it is taking it is searching for the second column ,so it is giving error where as in 3rd Query it is considering alias name as order by……
We can sort either by ordinal position or column / alias name
the query giving an error doesnt have any of them either
The second query is attempting to order the results by the second element in the Select clause which does not exist. The third query is attempting to order the results by the colum name of ‘2’ which does exist.
First query is sorting the data based on ‘first’ column, which actually is the only column. Second query is sorting the data based on ‘second’ column – which is not even there. Third query is sorting specifically on the column named ‘2’, which is the only column aliased as ‘2’.
Query 2: It’s throwing error because it’s not able find found 2nd column . Though query return only one column and it’s trying to sort with 2nd column.
Query 3: Here it is sorting with column name “2” which select query return. so it able to sort it.
query 1 is fine though it is sorting with column 1 and select query return it.
Query 2 throwing error though it’s try to sort with column column position 2 and not able to find the 2nd column though select return only one column.
query 3 sorting with column name “2” which is return by select query. so it’s fine .
In Sql Server you can order result set either column name or it’s index.
1. If you provide any integer number after the order by clause it will first find that number position column and ordering that column values.
2. If you provide column name then it will directly order that column values.
In Query 2: you have written order by 2, So it will go find that 2 position column but result set only returns 1 columns hence it will show error.
While in Query 3: You have written order by with its column name ’2′. So it will executed successfully. Because result set have column name ’2′.
If you don’t explicitly reference a column name (alias) but instead just use numbers in the GROUP BY clause, it will use those numbers to refer to the ordinal column number (i.e.: 1 references the first returned column, 2 references the second returned column, etc.).
In both the first and second examples, the order by clause sorts based on the ordinal column number: in the first example that’s column one, which exists; in the second example that’s column two, which doesn’t exist, and thus throws an error. In the third example the quotes are telling the compiler to sort based on a column named ‘2’, and you gave the only column that’s returned a name (alias) of ‘2’.
And of course I meant ORDER BY in the first paragraph, not GROUP BY …
Hi Pinal,
We have specified column name in order by. Otherwise, we have an option to specified the column position in table.
Query 1 : In this we have one column of value. so that order by 1 referred that column. Query 2 : In this also we have one column of value. so that order by referred column 2 is not available. Unable to ordered the column 2 from select list.
Query 3 : In this also we have one column of value. We specified alias name for that column so that order by referred column 2 is available. So that, it retrieves records correctly.
Hey Pinal,
In the 2nd query we are trying to order by the 2nd column in the order of the select statement while the select only contains 1 column.
In the third query we are trying to order by the column which has the name ‘2’ which in our case is the only column in the select statement.
Regards.
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.
Order by operation is over columns name, the second query doesn’t contain any column name 2 but 3rd rename as column 2
ORDER BY 1 in first query indicating to first column
ORDER BY 2 in second query indicating to second column which is not exist
ORDER BY 2 in third query firstly check second exist or not and if not exist then it check existence of the column name as 2
Query 3 is same as above query(it also works):
SELECT 2 AS ‘okay’
UNION ALL
SELECT 2 AS ‘okay’
ORDER BY ‘okay’
We are specifying column alias not the column index, that’s why query 3 is working.
In Query 2 we are referring to second column while in Query 3 we are referring to a column name ‘2’ as we had given the column alias as ‘2’