12 July 2007 09:02
tonyrogerson
Quick ORDER BY tip - prioritising the ordering based on column contents / dynamic ordering without dynamic SQL
On occaison you will need to prioritise the order, for instance, if you are ordering on a CompanyName and that column is NULLable or can be an empty string if you ORDER BY CompanyName you will get all the NULL's and empty strings first which might not be want you want. Other stuff you might want to do are a dynamic ORDER on a column or ASC/DESC based on a parameter to a stored procedure.
Anyway, a way round this:
select companyname
from registrations
where country = 'GB'
order by case when companyname = '' then 1 else 0 end, companyname
It's simple really, the CASE expression provides logic that alters the ordering.
If you are making things more complex, for example changing the order by according to a passed parameter...
declare @col_order sysname
set @col_order = 'idregistrations'
select top 100 companyname
from registrations
where country = 'GB'
order by case when @col_order = 'companyname' then companyname else idregistrations end, country
The above works because the resulting data type of the CASE will be bigint because idregistrations is bigint and you are ordering by bigint.
declare @col_order sysname
set @col_order = 'idregistrations'
select top 100 companyname
from registrations
where country = 'GB'
order by case when @col_order = 'companyname' then companyname else idregistrations end, country
The above does not work, it gives this error...
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to bigint.
Why, strange - looks fine doesn't it and values from idregistrations can not be obtained; however, the resulting data type is still bigint because bigint takes precendence over varchar.
You are actually trying to do this..
select 'abc' + 12345
Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'abc' to a column of data type int.
The fix is to either a) use dynamic SQL or b) write it like this...
declare @col_order sysname
set @col_order = 'companyname'
select top 100 companyname
from registrations
where country = 'GB'
order by case when @col_order = 'companyname' then companyname else null end,
case when @col_order = 'idregistrations' then idregistrations else null end,
country
You can also add ASC and DESC ....
declare @col_order sysname
set @col_order = 'companyname'
select top 100 companyname
from registrations
where country = 'GB'
order by case when @col_order = 'asc' then companyname else null end ASC,
case when @col_order = 'desc' then companyname else null end DESC,
country
The power of CASE is amazing - make sure you know it inside out!
Filed under: SQL Server