SQL Server Error Messages - Msg 108¶

Error Message¶

Server: Msg 108, Level 15, State 1, Line 1
The ORDER BY position number is out of range of the number of items in the select list.

Causes¶

As the message suggests, this error occurs when specifying the column index or position number in an ORDER BY clause in your SELECT statement and the column index or position number is either 0 or higher than the number of columns specified in the SELECT clause.

To illustrate, given the following table structure:

CREATE TABLE [dbo].[Customers] (
    [CustomerID]    INT NOT NULL IDENTITY(1, 1),
    [FirstName]     VARCHAR(100) NOT NULL,
    [LastName]      VARCHAR(100) NOT NULL
)

The following SELECT statement will produce the error:

SELECT [CustomerID], [FirstName], [LastName]
FROM [dbo].[Customers]
ORDER BY 4

Msg 108, Level 16, State 1, Line 3
The ORDER BY position number 4 is out of range of the number of items in the select list.

SELECT [CustomerID], [FirstName], [LastName]
FROM [dbo].[Customers]
ORDER BY 0

Msg 108, Level 16, State 1, Line 3
The ORDER BY position number 0 is out of range of the number of items in the select list.

Another not so obvious way of producing the error is as follows:

SELECT [CustomerID], [FirstName] [LastName]
FROM [dbo].[Customers]
ORDER BY 3 DESC

Msg 108, Level 16, State 1, Line 3
The ORDER BY position number 3 is out of range of the number of items in the select list.

It may look like 3 columns were specified in the last SELECT statement and the ORDER BY 3 DESC clause should not be causing an error but the missing comma between the [FirstName] and [LastName] makes the [LastName] the alias of the [FirstName] column. Executing this SELECT statement without the ORDER BY 3 DESC clause will produce only 2 columns, and not 3, where the name of the second column is “LastName” and not “FirstName”.

Solution / Work Around :¶

To avoid this error, make sure that the position number or column index specified in the ORDER BY clause is within the range of the number of items in the SELECT list. The following SELECT statement shows the position number or column index specified in the ORDER BY clause within the range of the number of items in the SELECT list.

SELECT [CustomerID], [FirstName], [LastName]
FROM [dbo].[Customers]
ORDER BY 3 DESC

Another way of avoiding this error is instead of specifying the position number or column index in the ORDER BY clause, specify the column name itself. The following SELECT statement performs the same task as specified above, but instead of using the position number or column index, the name of the column is specified:

SELECT [CustomerID], [FirstName], [LastName]
FROM [dbo].[Customers]
ORDER BY [LastName] DESC

An advantage of specifying the column name instead of the column index or position number is that even if new columns are added in the SELECT clause, the result will still be sorted by the column name specified in the ORDER BY clause. But if the column index or position number is used and a new column is added in the SELECT clause, the sorting of the result may change and may now be based on another column.

To illustrate, given the following table structure and SELECT statement:

CREATE TABLE [dbo].[Employees] (
    [EmployeeID] INT NOT NULL IDENTITY(1, 1),
    [FirstName] VARCHAR(100) NOT NULL,
    [LastName] VARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Salary] MONEY NOT NULL
)

SELECT [FirstName], [LastName], [Salary]
FROM [dbo].[Employees]
ORDER BY 3 DESC

This SELECT statement returns the list of employees sorted by their salary in descending order. If another column is added at the beginning of the SELECT statement, the result will now be sorted differently.

SELECT [EmployeeID], [FirstName], [LastName], [Salary]
FROM [dbo].[Employees]
ORDER BY 3 DESC

This SELECT statement will now return the list of employees sorted by the Last Name in descending order instead of by the salary.