Using ORDERBY in derived tables

In Microsoft SQL Server you are not permitted to use ORDERBY in a derived table unless you also provide a TOP(n) constraint.

Meaning that the following SQL is invalid

1. SELECT * FROM (SELECT EmployeeID FROM Employees ORDER BY EmployeeID DESC) AS e

This will throw an exception from SQL Server

1. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Based on the error message it is pretty obvious how to fix this.

1. SELECT * FROM (SELECT TOP 5 EmployeeID FROM Employees ORDER BY EmployeeID DESC) AS e

Now the query runs just fine and just top make sure that the sorting actually works we can return the result bottom-up.

EmployeeID
-----------
10
9
8
7
6

(5 row(s) affected)

But what if we want to return all rows and still apply sorting?

Many forum posts suggests the following approach.

1. SELECT * FROM (SELECT TOP 100 PERCENT EmployeeID FROM Employees ORDER BY EmployeeID DESC) AS e

The query still executes but the sorting is now completely omitted.

EmployeeID
-----------
10
3
4
8
1
2
6
7
5
9

The the solution to this is to provide a number to the TOP (N) clause that represents all the rows from the table.

The argument is a BIGINT and the highest possible value is 9223372036854775807.

Now if we change the query into this:

1. SELECT * FROM (SELECT TOP 9223372036854775807 EmployeeID FROM Employees ORDER BY EmployeeID DESC) AS e

We can now see that the result comes back as expected sorted by EmployeeID.

EmployeeID
-----------
10
9
8
7
6
5
4
3
2
1