Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

ORDER BY ColumnName vs ORDER BY ColumnNumber

Dec 27 2010 3:32AM by Madhivanan   

Pinal Dave in his blog posted this about using a column name and column number in the Order by clause. He made some valid points on why the second method should not be used. However, there is a case in which second method can be useful

I had already blogged about Dynamic PIVOT in which you can pass the queries, aggerate columns, etc. What if you want to order the resultset by the first column always?. You can use column number in the order by clause. So modify the procedure provided in the above post as follows

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) 

So my adivise is use it in a dynamic sql where column names are unknown

Tags: t-sql, sql_server, sqlserver, tsql, BRH, #TSQL, #SQLServer, order by,


Madhivanan
3 · 40% · 12909
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"ORDER BY ColumnName vs ORDER BY ColumnNumber" rated 5 out of 5 by 1 readers
ORDER BY ColumnName vs ORDER BY ColumnNumber , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]