Here is something interesting to think about

Create this table and insert these 2 rows

T-SQL
1
2
3
create table TableName(id int, name varchar(50))
insert TableName values(1,'bla')
insert TableName values(2,'bla2')
create table TableName(id int, name varchar(50))
insert TableName values(1,'bla')
insert TableName values(2,'bla2')

Now if you try to do something like this

T-SQL
1
2
3
4
5
   SELECT TOP 1 ID,Name
   FROM TableName
   ORDER BY Name
   UNION ALL
   SELECT 0,''
   SELECT TOP 1 ID,Name
   FROM TableName
   ORDER BY Name
   UNION ALL
   SELECT 0,''

You will get the following error

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘UNION’.

Interestingly, this same code will work if you use it in the following matter

T-SQL
1
2
3
4
5
6
7
8
9
SELECT TOP 1 *
FROM (
   SELECT TOP 1 ID,Name
   FROM TableName
   ORDER BY Name
   UNION ALL
   SELECT 0,''
) X
ORDER BY ID DESC
SELECT TOP 1 *
FROM (
   SELECT TOP 1 ID,Name
   FROM TableName
   ORDER BY Name
   UNION ALL
   SELECT 0,''
) X
ORDER BY ID DESC

This same code also works with Common Table Expressions

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH query AS (
    SELECT TOP 1 ID,Name
   FROM TableName
   ORDER BY Name
   UNION ALL
   SELECT 0,'')
 
 
  SELECT TOP 1
         x.id,
         x.name
    FROM query x
ORDER BY x.id DESC
WITH query AS (
    SELECT TOP 1 ID,Name
   FROM TableName
   ORDER BY Name
   UNION ALL
   SELECT 0,'')


  SELECT TOP 1
         x.id,
         x.name
    FROM query x
ORDER BY x.id DESC

My assumption is that the optimizer disregards any order by in the subquery. Still I find this interesting because usually a query will work, you stick it inside another piece of code and it will complain about something

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum