|
|
Error Message
Server: Msg 169, Level 15, State 1, Line 1
A column has been specified more than once in the order
by list. Columns in the order by list must be unique.
Causes
The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. The column names referenced in the ORDER BY clause must correspond to either a column in the SELECT list or a column of the table in the FROM clause without any ambiguities.
|
If column names are aliased in the SELECT list, only the alias name can be used in the ORDER BY clause. Similarly, if table names are aliased in the FROM clause, only the alias names can be used to qualify their columns in the ORDER BY clause.
Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set. The sort columns can be specified as a name or column alias, or a non-negative integer representing the position of the name or alias in the SELECT list.
When specifying the sort columns, the same column, either specified by the column name or by the non-negative integer representation of the position of the column in the SELECT list, cannot appear more than once in the ORDER BY list, or this error message will be encountered.
To illustrate, the following SELECT statements with the ORDER BY clause will generate this error message:
-- Column Names Specified More Than Once
SELECT * FROM [dbo].[sysobjects]
ORDER BY [XType], [Name], [XType]
Msg 169, Level 15, State 1, Line 1
A column has been specified more than once in the order by list.
Columns in the order by list must be unique.
-- Column Name and Non-Negative Integer Representing the Position of the Same Column
SELECT * FROM [dbo].[sysobjects]
ORDER BY [Name], 1
Msg 169, Level 15, State 1, Line 1
A column has been specified more than once in the order by list.
Columns in the order by list must be unique.
Solution / Work Around:
As the error message suggests, a column cannot be specified more than once in the ORDER BY list, specified either as the name of the column or as the non-negative integer representation of the position of the column in the SELECT list.
Using the SELECT statements earlier, by simply removing the repeated column, this error message will be avoided:
SELECT * FROM [dbo].[sysobjects]
ORDER BY [XType], [Name]
SELECT * FROM [dbo].[sysobjects]
ORDER BY [Name]
This is but a simple example on how this error can be encountered and avoided. On more complex SELECT statements that contain a lot of columns in the ORDER BY clause which uses both the column name and the non-negative integer representation of the position of the column in the SELECT list, it might be a little bit harder identifying the column that is causing this error. One way to avoid this error is to always use just the name of the column and not mix the ORDER BY clause with the column position.
|