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.