SQL Server Error Message
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.
Reason for the Error
In SQL Server, you can use the column index in the ORDER BY clause. For example, In the below SQL Query, ORDER BY 1 uses the column index 1 , uses the first column ID for sorting.
SELECT STD.* FROM [dbo].[Students] AS STD ORDER BY 1
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.
For example , the below query will result in the error.
SELECT STD.* FROM [dbo].[Students] AS STD ORDER BY 4
Solution
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.
SELECT STD.* FROM [dbo].[Students] AS STD 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.
