Numbers in ORDER BY Clause

One of my friends asked me “What does it mean when a number is used in the order by clause?” An example is

Order by 1

Well. Numbers in ORDER BY clause denote two different meanings based on how they are used. Let us create the following simple data set

create table #customers(cust_id int, cust_name varchar(100))
insert into #customers (cust_id,cust_name)
select 1,'Balaji' union all
select 2,'Madhivanan' union all
select 3,'Arumugam'

Now consider the following query

select cust_id,cust_name from #customers Order by 1

The result is

cust_id      cust_name
-------      ------------------
1            Balaji
2            Madhivanan
3            Arumugam              

consider another query

select cust_id,cust_name from #customers Order by 2

The result is

cust_id      cust_name
-------      ------------------
3            Arumugam
1            Balaji
2            Madhivanan

What it means that when you specify the number in ORDER by clause, the result set is sorted based on the ordinal position of the columns specified in the SELECT statement. In the first example it is sorted by cust_id and in the second example it is sorted by cust_name.

But it is entirely different if the numbers are used in CASE expression in ORDER BY clause.

Consider the following example

select cust_id,cust_name from customers 
order by 
case when cust_name='Madhivanan' then 1 else 2 end

The result is

cust_id      cust_name
-------      ------------------
2            Madhivanan
3            Arumugam
1            Balaji

The above indicates that if the cust_name is Madhivanan, make it as first row and others in any order.

So numbers in ORDER BY clause refer the ordinal position of the columns if specified directly or assign the numbers as part of result set and sort it based on the numbers if specified in CASE expression.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s