The ORDER BY position number is out of range of the number of items in the select list

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. 

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.

This error usually happens when you are using the index in the ORDER BY clause instead of the column name and you have either used 0 or the position that is higher than the number of columns specified in the SELECT clause.

Use AdventureWorks2017
Go

Select FirstName,MiddleName,LastName
from Person.Person
Order By 4

OutPut:

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.

To avoid this, error make sure that the position number or the column index specified in the ORDER by clause is with-in the range of items in the SELECT clause.

The best practice is to use the column name instead of the index as shown below.

Use AdventureWorks2017
Go

Select FirstName,MiddleName,LastName
from Person.Person
Order By FirstName

One of the advantages of specifying the column name instead of the index is that even you add new columns in to the SELECT clause, you will have the desired result with-out any errors. Using index for sorting will result in undesired results when you add new columns in to your SELECT clause.

The column prefix does not match with a table name or alias name used in the query

Msg 107, Level 16, State 3, Line 1

The column prefix does not match with a table name or alias name used in the query.

The actual Error message varies with the Alias being used. The usual message is as shown below.

The column prefix '%.*ls' does not match with a table name or alias name used in the query.

The % in the above can be replaced with the alias that was used and caused the issue.

For example, below are 2 scenarios that will lead to the SQL Error msg 107.

• When the Column Prefix doesnot correspond to the table or alias name that is used in the SQL query. The below query leads to the error. 

Use AdventureWorks2017
Go

SELECT P.*
FROM Person.Person
where FirstName='Adam'

Output:

Msg 107, Level 15, State 1, Line 7

The column prefix 'P' does not match with a table name or alias name used in the query.

In this query, the alias P cannot be identified and will result above error

• The table name is specified as a column prefix where as the alias name is supplied in the FROM clause. 

Use AdventureWorks2017
Go

SELECT Person.*
FROM Person.Person As PR
where FirstName='Adam'

Output:

Msg 107, Level 15, State 1, Line 7

The column prefix 'Person' does not match with a table name or alias name used in the query.

To avoid this error, always make sure that the table name used as a prefix of a column in a query exists in the FROM clause.

Use AdventureWorks2017
Go

SELECT Person.*
FROM Person.Person
where FirstName='Adam'

Also, to avoid the second cause of this error, once you assign an alias to a table in the FROM clause, make sure to use that alias in a column prefix and not the original table name:

Use AdventureWorks2017
Go

SELECT PR.*
FROM Person.Person As PR
where FirstName='Adam'