Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server
Tip/Trick

Order by Clause with Column_Index instead of Column Name

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
18 Jul 2011CPOL1 min read 17.7K   3   1
A tip enable you to learn how to use Column_Index instead of Column Name with Order by clause
Its a very general and regular ways to use Order by clause with Column Name to Specifies the sort order used on columns returned in a SELECT statement.
Syntax :-
order by columnname asc/desc


I am also using the same method but do you know we can use Column Index (Integer representing the position of column name) instead of specifying the name or column alias in a SQL Server Order By expression.
As:-
order by Column_Index_Number asc/desc

we can use both queries because both of these queries having the same results.

let check both syntax and result too
here I am going to query based on AdventureWork database

Example :- Getting top 10 value from Employee table based on EmployeeID in asc order
select top  10 * from HumanResources.Employee order by EmployeeID asc

output will be as given below :-


when Using Column Index instead of Column Name for same query
Note :- Column Index can be changed as 1,2,3,etc based on column name condition
select top  10 * from HumanResources.Employee order by 1 asc

then output will be same as given below :-


Reference Link :- Matt Berseth Articles[^]

Note :- ORDER BY clause is only Clause where we can you use the ordinal position[Column Numbers], because it's based on the column(s) specified in the SELECT clause.
its generally recommended that to use Column Name instead of Column Number. but in some cases using Column Number can be useful like it can be used it in a dynamic sql where column names are unknown.
for getting practical way you can follow this[^] blog.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralIts good to know but this not making any difference on execu... Pin
am.net25-Jul-11 19:30
am.net25-Jul-11 19:30 
Its good to know but this not making any difference on execution plan. They both query taking same time to display results.

BTW.. good one Smile | :)

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.