SQL SERVER – Simple Puzzle with UNION

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 (http://blog.sqlauthority.com)

About these ads

67 thoughts on “SQL SERVER – Simple Puzzle with UNION

  1. 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.

  2. 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 :)

  3. 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.

  4. 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

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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

  10. 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.

  11. 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′

  12. 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.

  13. 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.

  14. 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′

  15. 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.

  16. 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″.

  17. 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.

  18. 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

  19. 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.

  20. 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

  21. 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……

  22. 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.

  23. 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′.

  24. 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.

  25. 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 .

  26. 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′.

  27. 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′.

  28. 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.

  29. 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.

  30. Pingback: SQL SERVER – Simple Puzzle with UNION – Part 2 | Journey to SQL Authority with Pinal Dave

  31. Order by operation is over columns name, the second query doesn’t contain any column name 2 but 3rd rename as column 2

  32. 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

  33. 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.

  34. 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′

  35. 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..
    :)

  36. 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

  37. 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

  38. Pingback: SQL SERVER – Simple Puzzle with UNION – Part 3 | Journey to SQL Authority with Pinal Dave

  39. Pingback: SQL SERVER – Simple Puzzle with UNION – Part 4 | Journey to SQL Authority with Pinal Dave

  40. Pingback: SQL SERVER – Simple Puzzle with UNION – Part 5 | Journey to SQL Authority with Pinal Dave

  41. Pingback: SQL SERVER – Five Puzzles around UNION – Participate in All Five | Journey to SQL Authority with Pinal Dave

  42. 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″

  43. 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″

  44. 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.

  45. 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.

  46. 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.

  47. 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.

  48. 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.

  49. 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.

  50. 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.

  51. 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.

  52. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s