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. This one is a simple puzzle with UNION and ORDER BY.

First let us execute following SQL.

Query 1: A Simple Puzzle with UNION

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 to this simple puzzle with UNION – I will post the answer with due credit in future blog posts.

A Small Hint and a Good Habit

Here is a small hint. In Query 2, the number after ORDER BY is not a value. SQL Server reads it as a column position, so ORDER BY 2 means sort by the second column. Our query returns only one column, and that is exactly what the first error message says. Now look closely at Query 3 and ask yourself what the quotes change.

This puzzle also teaches a good habit. In real code, I avoid sorting by column position. If someone adds or moves a column in the SELECT list later, the sort silently changes. Give every column a clear alias and sort by that name instead. Your future self will thank you.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.