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

Fun with ORDER BY clause

Oct 10 2011 2:01AM by Madhivanan   

You can use Ordinal position in the ORDER BY Clause for sorting the resultset. The ordinal position refers the column position in the SELECT statement. Consider the following select statement

select * from
(
select 17 as a,2 as b 
union all
select 1 as a,25 as b 
) t 
order by 1

The above sorts the resultset based on the column number 1 (a) in the select statement Consider the following statement

select 1 as a,2 as b order by COUNT(*)

The count(*) will return 1 and the resultset should be sorted by first column. Let us replace count(*) with value

select 1 as a,2 as b order by 1

It works fine. Now when you specify 5 in the order by clause

select 1 as a,2 as b order by 5

It throws an error

Msg 108, Level 16, State 1, Line 1
The ORDER BY position number 5 is out of range of the number of items in the select list.

Ok. Execute this code and see the result

select count(*)+4

It returns 5 as count(*) is 1 Now replace 5 with count(*)+4 in the select statement and you would expect the same error. Right?

select 1 as a,2 as b order by count(*)+4

But note that the above does not return error message even though there is no 5th column in the select statement The following select statement throws an error

select @@SERVERNAME+8092834 

But when the above is used in an order by clause it does not throw an error

select 1 as a, 2 as b order by @@SERVERNAME+8092834 

You can also use a subquery in the ORDER BY Clause as below

select 1 as a, 2 as b order by (select 'test')

You can use multiple queries too

select 1 as a, 2 as b order by (select 'test'),(select 7823)

This query works fine

select 1 as a, 2 as b  order by @@SERVERNAME+8092834 

But when there are more than a row, the following throws an error

select * from (select 1 as a, 2 as b union select 5,76) as t order by @@SERVERNAME+8092834 

All the below queries will work fine

select a,b from
(
select 1 as a,2 as b 
union all
select 1 as a,2 as b 
) t 
group by a,b order by SUM(1)+'a'


select d from
(
select 2 as d union all select 1
) t
order by (select 100) /45+@@CONNECTIONS-90 						


select d from
(
select 2 as d union all select 1
) t
order by (select 100),(select 'test'),(select max(name) from sysobjects)

select d from
(
select 2 as d union all select 1
) t
order by (select max(n) from (select 'test' as n union all select '') t) asc



select 1 as a,2 as b order by @@SERVERNAME * count(*) 

select 1 as a,2 as b order by @@SERVERNAME * count(*)*'test'

select 1 as a,2 as b order by @@SERVERNAME * count(*)*'test'/56.324

So from these examples it is clear that SQL Server in some cases omits the expression using system functions completely and ignores the errors too. So you need to beware of this behaviour

Tags: t-sql, sql_server, sqlserver, tsql, order by,


Madhivanan
3 · 40% · 12936
13
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

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