# Use of IIF for sorting records in SQL Server

Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 2911

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

## 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.

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.