Use of IIF for sorting records in SQL Server

Rajnilari2015
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 475 red flag

In this article, we will look into how to sort records using the IIF function.


 Download source code for 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.

Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,Microsoft_MVP,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)