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:

SQL SERVER - Simple Puzzle with UNION puzzunion1

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.

SQL SERVER - Simple Puzzle with UNION puzzunion2

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:

SQL SERVER - Simple Puzzle with UNION puzzunion3

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)

, ,
Previous Post
SQLAuthority News – 10 SQL in Sixty Days Video in 10 Days – Contest to Win 10 Cool Gifts
Next Post
SQL SERVER – Simple Puzzle with UNION – Part 2

Related Posts

63 Comments. Leave new

  • Oder by is working for sort by column or column sequence and the priority is
    1- Column name then
    2- Sequence

    Here in query 2 there is no column having name 2 but in 3 column having name hence no error

    Reply

Leave a Reply

Menu