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

Numbers in ORDER BY Clause

Oct 31 2014 12:00AM by Madhivanan   

You can use numbers in ORDER BY Clause so that it will sort the result set based on the ordinal position of the column. But when numbers are used in a CASE Expression, they do not refer the ordinal position of the columns but they behave like ordering based on derived column with as expression.


Let us consider the following data

create table #test(mobile_no int, model_name varchar(100))

truncate table #test
insert into #test(mobile_no,model_name)
select 1,'Samsung Galaxy' union all
select 2,'Nokia Lumia' union all
select 3,'Apple 5s' union all
select 4,'Micromax Canvas' union all
select 5,'Moto X' 

1 Numbers in ORDER BY Clause

select * from #test order by 2
The result is
mobile_no   model_name
----------- --------------------
3           iPhone 5s
4           Micromax Canvas
5           Moto X
2           Nokia Lumia
1           Samsung Galaxy
As you see the results are ordered by Name because it is a second column
   
2 Expression in ORDER BY Clause

select * from #test order by case when model_name='Nokia Lumia' then 1 else 2 end
The result is
mobile_no   model_name
----------- ----------------------
2           Nokia Lumia
3           iPhone 5s
4           Micromax Canvas
5           Moto X
1           Samsung Galaxy
As you see the Value Nokia Lumia comes first followed by others. This is logically similar to the following query
select *,case when model_name='Nokia Lumia' then 1 else 2 end as ordering from #test order by ordering 
The result is
mobile_no   model_name              ordering
----------- -------------------------------------------------
2           Nokia Lumia             1
3           iPhone 5s               2
4           Micromax Canvas         2
5           Moto X                  2
1           Samsung Galaxy          2
Similarly you can use the following code to bring Nokia Lumia and Moto X on top of the reultset
select * from #test order by case when model_name in ('Nokia Lumia','Moto X') then 1 else 2 end
The result is
mobile_no   model_name
----------- ---------------------
2           Nokia Lumia
5           Moto X
1           Samsung Galaxy
3           iPhone 5s
4           Micromax Canvas
So based on how you use, Numbers in ORDER BY clause behave differently

Tags: 


Madhivanan
2 · 40% · 13039
3
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

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