Expression type int is invalid for COLLATE clause

When I run the following in SQL Server it runs fine:
select * from TableA order by First_Candidate collate SQL_Latin1_General_CP1_CI_AS

But when I do the order by the ordering of the column, then I get the error:
"Expression type int is invalid for COLLATE clause. "
select * from TableA order by 7 collate SQL_Latin1_General_CP1_CI_AS

How can you overcome this?
LVL 6
anushahannaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anilallewarCommented:

The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.
Can you try replacing the "ORDER BY 7" with "ORDER BY <Name of column>"?
 
Patrick MatthewsCommented:
Silly question: is First_Candidate the 7th column in TableA, or is some other column in the 7th position?

If it's someother column, then I imagine that that column is [n][var]char, for which specifying a collation makes sense...
halfbloodprinceCommented:
COLLATE is applicable only for columns of the char, varchar, nchar, and nvarchar data types.

For more info:
http://msdn.microsoft.com/en-us/library/ms188385.aspx
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

anushahannaAuthor Commented:
Yes, the 7th column is varchar. that is my point- as to why i am not able to just say order by 7 with the collate.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> as to why i am not able to just say order by 7 with the collate.

column order no. is added to simplify typing your large column names..
In order to specify collate keyword or any other operation, you need to specify the column name in order to work..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anushahannaAuthor Commented:
ok. thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.