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%'

ss1

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

qq1
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

qq2In 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

qq4

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.