Mar 18, 2012

The SELECT item identified by the ORDER BY number contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.


Let us assume we have created a table and inserted some data into using following sql query:

CREATE TABLE tblSource(
    ID INT IDENTITY PRIMARY KEY,
    vcData VARCHAR(500)
)

INSERT INTO tblSource VALUES('PC'),('Laptop'),('Mobile')

If we will execute following sql query:

DECLARE @Position INT = 1
SELECT * FROM tblSource  ORDER BY @Position  DESC

We will get error message :

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

Cause: When we use variable in the ORDER BY clause then it must be a column name. It cannot be column position.

Solution:

Use the column name instead of column position. For example:

DECLARE @Col_Name VARCHAR(128) = 'ID'

SELECT *
FROM tblSource 
   ORDER BY CASE 
    WHEN @Col_Name = 'ID' THEN CAST(ID AS SQL_VARIANT)
    WHEN @Col_Name = 'vcData' THEN CAST(vcData AS SQL_VARIANT) END  ASC



Output:

ID
vcData
1
PC
2
Laptop
3
Mobile

6 comments:

  1. Although this doesn't error, it also doesn't work. In this case the order by clause is simply ignored. You'll notice that the output is not sorted by ID descending. It's equivalent to the following...

    SELECT *
    FROM tblSource

    ReplyDelete
    Replies
    1. Yes, you are correct. Now I have changed the script. It will work.

      Delete
  2. Anonymous6/25/2013

    thanks a lot. It is working great.

    ReplyDelete
  3. Anonymous8/06/2013

    Thanks for this tip. Very useful!

    ReplyDelete
  4. Anonymous1/07/2014

    hnmmmmmmmmmmm

    ReplyDelete
  5. Anonymous3/03/2015

    try for dynamic query it is better

    ReplyDelete