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% · 12862
13
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

13  Comments  

  • I executed all mentioned queries. But one query hitting me again and again. Aggregation in ORDER BY....how this is possible?

    commented on Oct 10 2011 1:11PM
    Robert Dennyson
    11 · 14% · 4420
  • 1.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

    I'm pretty sure you're wrong to think SQL Server uses functions/subqueries in order by clauses and that returns a number as a reference to a retrieved column. IMO, using a number to refer a retrieved column is a strict (shorhand) syntax.

    For instance if you order by CASE [...] THEN 1 ELSE 2 END you don't expect the sorting to happen for column 1 or column 2 depending on a condition. Or even simplier: [code] SELECT a, b FROM ( SELECT 2, 1 UNION ALL SELECT 2, 1 UNION ALL SELECT 2, 1 ) X (a, b) ORDER BY a [/code]

    Don't tell me you'd expect SQL server to sort data on a wich is 2 wich mean it actually sort data on b which is 1 which mean it actually... ^^

    commented on Oct 11 2011 2:22AM
    Sergejack
    41 · 4% · 1395
  • Yeah, I got it now. Thank you both... :)

    commented on Oct 11 2011 3:10AM
    Robert Dennyson
    11 · 14% · 4420
  • Sergejack , I am aware of what you say. But the point of this blog post is about the behavior involved in ORDER BY Clause for invalid expressions. In some cases SQL Server omits the expression in some cases it evaluates. You can see it from the examples posted. CASE WHEN expression in an ORDER BY Clause will return values and SQL Server will never consider it as the ordinal position.

    commented on Oct 11 2011 8:43AM
    Madhivanan
    3 · 40% · 12862
  • interesting post

    commented on Oct 12 2011 1:05PM
    Alok Chandra Shahi
    69 · 3% · 810
  • To the best of my knowledge in all your queries:

    Every order clauses which works are either valid... e.g: (select 100) /45+@@CONNECTIONS-90

    either apply on one row. e.g: 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

    The only case an invalid order by clause fail is when applied on several rows.

    Which leads us to the conclusion: SQL Server is clever enough not to even care about trying to order a single row result set.

    commented on Oct 13 2011 2:10AM
    Sergejack
    41 · 4% · 1395
  • BTW, try this :

    declare @two int set @two = 2

    select a,b from ( select 1 as a,3 as b union all select 1 as a,2 as b ) t order by @two, a

    It should put on light that using a number to refer a retrieved column is a strict (shorhand) syntax where no evaluation can take places.

    commented on Oct 13 2011 2:13AM
    Sergejack
    41 · 4% · 1395
  • << SQL Server is clever enough not to even care about trying to order a single row result set.

    >

    That is the point of my point and so is title as Fun :)

    commented on Oct 13 2011 2:14AM
    Madhivanan
    3 · 40% · 12862
  • Sergejack, It is not possible to use a user defined variable in the ORDER BY Clause. However you can use system variables which are omitted by default. This will work and see that the first expression is omitted in the ORDER BY clause

    declare @two int set @two = 2 select @two , a,b from ( select 1 as a,3 as b union all select 1 as a,2 as b ) t order by @@CONNECTIONS+@two , a

    commented on Oct 13 2011 2:20AM
    Madhivanan
    3 · 40% · 12862
  • The behaviour is logical in most queries. I'm only puzzled by the following:

    SELECT d FROM (SELECT 2 AS d UNION ALL SELECT 1) t ORDER BY (SELECT 1) /0

    SELECT d FROM (SELECT 2 AS d UNION ALL SELECT 1) t ORDER BY 1/0

    Why does the first one work and the second one doesn't work?

    commented on Oct 14 2011 1:18AM
    Razvan Socol
    177 · 1% · 278
  • Good Post Madhivanan... Yes I could be Possible in SQL Server (90) compatibility mode .

    commented on Oct 16 2011 12:52AM
    Shivendra Kumar Yadav
    61 · 3% · 924
  • Nice post..

    commented on Apr 11 2012 3:22AM
    Hardik Doshi
    20 · 9% · 2845
  • Thank-you for the great post, Madhivanan!

    commented on Jun 2 2012 2:28PM
    Nakul Vachhrajani
    4 · 36% · 11543

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]