Use of IIF for sorting records in SQL Server

Introduction

IIF function is introduced since SQL Server 2012. It returns a value as per the specified Boolean condition. Earlier we have written an article on this. In this article, we will look into how to sort records using the IIF function.

Using the code

Let us say we have a set of records generated by using the below SQL script

Declare @T  Table(Codes Varchar(50))
Insert Into @T 
Select '>120' Union All
Select '1-30' Union All
Select 'x' Union All
Select '31-60' Union All
Select '61-90' Union All
Select '91-120' 

Select *
From @T

/* Result */
Codes
------
>120
1-30
x
31-60
61-90
91-120

Objective

Our objective is to sort the record in such a way that the final output resemble

N.B.~ The "Codes" are static and not dynamic. But we cannot create an Identity column for it since it's being created at run time (in the real time situation).

Solution

We proposed the below solution for the aforementioned problem

SELECT *
FROM @T
ORDER BY	IIF(Codes = 'x',1,
		IIF(Codes = '1-30',2,
		IIF(Codes = '31-60',3,
		IIF(Codes = '61-90',4,
		IIF(Codes = '91-120',5,6)))))

What we are doing is that, we are assigning the numbers to the "Codes" by using the IIF function and then performing the sorting.

Another way of doing the same follows

SELECT Codes 
FROM
	(
		SELECT	Codes
			, Rn=Row_Number() Over
				(
					ORDER BY	IIF(Codes = 'x',1,
							IIF(Codes = '1-30',2,
							IIF(Codes = '31-60',3,
							IIF(Codes = '61-90',4,
							IIF(Codes = '91-120',5,6)))))
				)
		FROM @T)X
ORDER BY Rn

This time we applied the sorting inside the Row_Number() function.

The output the both the cases will be same

N.B.~ The problem can be addressed in multiple ways. We have presented one way here for the same.

Conclusion

This article taught us how use IIF with Order By and Row_Number function. Hope this will be helpful. Thanks for reading. Zipped file is attached herewith.