Numbers in ORDER BY Clause

Posted on by madhivanan

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.

Best Practices – Use Unambiguous Date formats

Posted on by madhivanan

I see that lot of sql developers do not understand how to effectively express Date/Datetime values in the queries. Many of them tend to use regional date formats.

Let us see what happens when regional date format is used. In India the regional date format is dd/MM/yyyy

declare @date datetime
set @date='10/06/2016'
select @date as date

The result is

date
-----------------------
2016-10-06 00:00:00.000

When you run the following

declare @date datetime
set @date='16/06/2016'
select @date as date

The result is

Msg 242, Level 16, State 3, Line 6
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

As you see, the input is given in the form dd/MM/yyyy which means 10th June 2016, but it is actually stored as 6th October,2016. The second code throws an error because 16 is not a valid month. Where is the problem? The problem is using the regional date formats. The default date format is mdy and regional inputs should be expressed in that format only. But if the date format is different, you need to change the expression again.

set dateformat dmy
declare @date datetime
set @date='10/06/2016'
select @date as date

The result is

date
-----------------------
2016-06-10 00:00:00.000

Now it works fine. So based on the server’s date format you need to change the expression. How do we solve this problem? Use Unambiguous date format.

In SQL Server, there are three Unambiguous date formats

YYYYMMDD
YYYYMMDD HH:MM:SS
YYYY-MM-DDTHH:MM:SS

Now run the following and see the result

set dateformat dmy
declare @date datetime
set @date='20160610'
select @date as date
GO
set dateformat mdy
declare @date datetime
set @date='20160610'
select @date as date
GO
set dateformat ymd
declare @date datetime
set @date='20160610'
select @date as date

The result is

date
-----------------------
2016-10-06 00:00:00.000

So it does not matter what the server’s date format is, the unambiguous date formats always work as expected. So you should always express these formats to avoid error/unexpected result