Using CASE WHEN in ORDER BY clause

Question

Post reply

Using CASE WHEN in ORDER BY clause

rhd110

Ten Centuries

Points: 1062
More actions
August 29, 2011 at 2:40 am

#247093

Hi Friends,
MS SQL Server has given us a functionality of using CASE WHEN
in ORDER BY clause.
Can any one explain me what is the need, use of that with concrete examples.
Mainly how will it affect the output resultset.
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

You can use it to avoid dynamic queries.

Example:

create table dbo.tbSort(id int identity(1, 1), Country varchar(50), City varchar(50))

go

insert into dbo.tbSort(Country, City) values('India', 'Delhi')

insert into dbo.tbSort(Country, City) values('China', 'Beiging')

insert into dbo.tbSort(Country, City) values('Japan', 'Tokyo')

insert into dbo.tbSort(Country, City) values('Germany', 'Berlin')

insert into dbo.tbSort(Country, City) values('USA', 'Washington')

insert into dbo.tbSort(Country, City) values('Canada', 'Ottava')

go

create procedure dbo.spSort(@SortOrder varchar(50))

as

select * from dbo.tbSort order by case when @SortOrder = 'Country' then Country else City end

go

exec dbo.spSort @SortOrder = 'City'

go

exec dbo.spSort @SortOrder = 'Country'

go

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

You can specify your own order. For example in the fallowing query you’ll see all the sales from the U.S first and after those records you’ll see all the rest of the sales order by country. Without the case statement all sales from US wouldn’t be first

select count(*) as NumOfSales, SST.CountryRegionCode, SST.Name as CountryName

from Sales.SalesTerritory SST inner join Sales.SalesOrderHeader SSOH on SST.TerritoryID = SSOH.TerritoryID

group by SST.Name, SST.CountryRegionCode

order by CASE WHEN SST.CountryRegionCode = 'US' then 1 ELSE 2 end, SST.Name

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

rhd110 Ten Centuries Points: 1062 More actions · Answer 3

Thanks a lot for the reply.

In the above e.g 1 and 2 represent column no (sequence).

I have seen in queries people using 0.1 and 0.01 in case when

e.g

order by CASE WHEN SST.CountryRegionCode = 'US' then 0.1 ELSE 2 end, SST.Name

What does 0.1 represent then ??

Thanks in advance.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

Here 0.1 does not represent anything. It is just a trick to make the row appear first.

If you want, you can any other value, for example:

...order by CASE WHEN SST.CountryRegionCode = 'US' then 'A' ELSE 'B' end, SST.Name