Using Order by Clause with Column_Index instead of Column Name

RaviRanjanKr
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 7045 red flag
Rating: 5 out of 5  
 2 vote(s)

This article enable you to learn how to use Column Index Number instead of Column Name in the order by clause and where it should be used.

Introduction


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 :-
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) 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:- 

SELECT column_name(s)
FROM table_name
ORDER BY column_Index_Number(s) 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 :- 

Order by Clause with Column Name


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 :-

Order By Clause with Column Index Number

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.

Where to Use Column Index Number


In some cases using Column Number can be useful like it can be used it in a dynamic sql where column names are unknown.
suppose you need to create a dynamic Pivot stored procedure task in which user want the result set with order by as very first column name in anyways. then in such type of condition what will you do if you will not knowing about CoColumn Name.
In such type of Condition we can use Order by clause with Column Index Number.

Ok but how to use


You need to create Dynamic Pivot procedure( A Precompiled Object)  for that as given below 

create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100), 
@Summaries varchar(100)
) as 
declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')
 
create table #pivot_columns (pivot_column varchar(100))
Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)
select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns
select @sql=
'
    select * from 
    (
        '+@select+'
    ) as t 
    pivot 
    (
        '+@Summaries+' for pivot_col in ('+@pivot+')
    ) as p
ORDER BY 1
' 
exec(@sql)
Its better to have Northwind database to explore this.
you can navigate given link to download Nothwind database 

after downloading Database attach it to your SQL server Management Studio
and then after  execute dynamic_pivot stored procedure

after executing it you can also verify its existence. take a look how


Now execute given code
EXEC dynamic_pivot
'SELECT e.FirstName, o.OrderDate FROM Employees as e
INNER JOIN Orders as o ON (e.EmployeeID=o.EmployeeID) ',
'Year(OrderDate)',
'Count(OrderDate)'
while executing given code you will get error like 

Incorrect syntax near 'pivot'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

don't worry you are just getting waring message because of Database Compatible level. you can change it as suggested given below :- 

How to Change Compatible Level

Now Execute upper code again. it will run successfully and you will get out of result set in ordered way. take a look 

 The Result set using Order by Clause Pivot


Now you can see that the out put comes in order way. you can change order but for that you need to edit dynamic_pivot Stored procedure by using alter keywords.

Conclusion 


Its strongly recommended every where that to use Column Name with order by clause but some time we need to use Column Index Number instead of Column Name. 

Reference
http:// beyondrelational.com/blogs/madhivanan/archive/2010/12/27/order-by-columnname-vs-order-by-columnnumber.aspx

Page copy protected against web site content infringement by Copyscape

About the Author

RaviRanjanKr
Full Name: Ravi Ranjan Kumar
Member Level: Starter
Member Status: Member
Member Since: 10/12/2010 9:08:36 AM
Country: India
Thanks Ravi Ranjan Kumar http://raviranjankr.wordpress.com
http://raviranjankr.wordpress.com
CodeProject MVP | MCTS | CS Student I am always excited and keen Interested in learning and sharing knowledge. http://about.me/RaviRanjanKr

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)