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 orderselect 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 :-
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 :-
Now Execute upper code again. it will run successfully and you will get out of result set in ordered way. take a look
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