The CASE expression in ORDER BY clause in SQL Server 2012 – part 2
In this post You will be continue your adventure with CASE
expression in SQL Server 2012.
The CASE
expression in ORDER BY clause may have two formats:
– simple
– searched
The simple expression
has input expression or name of column, it is syntax for it:
CASE input WHEN when_exression THEN result ... [ ELSE default_result ] END
But searched expression
has not any value or expression after CASE
word,it is syntax for it:
CASE WHEN boolean_expression THEN result ... [ ELSE default_result ] END
The ELSE
is optional in CASE
expression.
The first example will be present data from AdventureWorks2012
database from Person
table.
We select data of people their lastname begin with Va
letters.
USE AdventureWorks2012; GO SELECT [Title], [FirstName], [LastName] ,YEAR([ModifiedDate]) FROM [AdventureWorks2012].[Person].[Person] WHERE [Title] is NOT NULL AND [LastName] LIKE 'Va%'
In second example we use CASE
expression in searched expression format
in ORDER BY
clause.
USE AdventureWorks2012; GO SELECT [Title], [FirstName], [LastName] ,YEAR([ModifiedDate]) as year FROM [AdventureWorks2012].[Person].[Person] WHERE [Title] is NOT NULL AND [LastName] LIKE 'Va%' ORDER BY CASE WHEN Title='Ms.' THEN FirstName ELSE LastName END DESC
This data are sort by value of title column and in Ms.
title group sort by FirstName
column descending, but if this column data is is not equal Ms.
is sort by LastName
column descending. If You would like to sort this data in group data with Ms.
titile ascending but in group data with other titile then Ms.
descending, then You may use two CASE
expression:
USE AdventureWorks2012; GO SELECT [Title], [FirstName], [LastName] ,YEAR([ModifiedDate]) as year FROM [AdventureWorks2012].[Person].[Person] WHERE [Title] is NOT NULL AND [LastName] LIKE 'Va%' ORDER BY CASE WHEN Title='Ms.' THEN FirstName END, CASE WHEN Title!='Ms.' THEN LastName END DESC
In this situation one group with Ms.
title is sort by FirstName
column ascending, but group without Ms.
title is sort by LastName
column descending.
In ORDER BY
clause You may either use simple
format CASE
expression.
USE AdventureWorks2012; GO SELECT [Title], [FirstName], [LastName] ,YEAR([ModifiedDate]) as year FROM [AdventureWorks2012].[Person].[Person] WHERE [Title] is NOT NULL AND [LastName] LIKE 'Va%' ORDER BY CASE Title WHEN 'Ms.' THEN FirstName END, CASE WHEN Title!='Ms.' THEN LastName END DESC