# Sort records based on Numeric Ranking in SQL Server

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

This article will discuss some approach in order to sort the records based on their numeric ranking.

## Introduction

Let's say we have a table as under

which is generated by using the below TSQL query

```DECLARE @T TABLE(

Id INT IDENTITY(1,1),

RangeDescription VARCHAR(20)

)

--Populate some values
INSERT INTO @T SELECT '200' UNION ALL
SELECT '45-90' UNION ALL
SELECT '190-200' UNION ALL
SELECT '70-100' UNION ALL
SELECT '30-50' UNION ALL
SELECT '50-30'

--Project the records
SELECT *
FROM @T
```

The objective is to sort the "RangeDescription" field in Descending order so that the final output resemble

Apparently if we do a simple "Order By RangeDescription", we receive the below

This happens because the "RangeDescription" column is of "VARCHAR" type and hence the Sorting is happening based on ASCII characters. Example, the ASCII of 0 is 48 and of 9 is 57. Henceforth is such a behavior.

But this article will discuss some approach in order to sort the records based on their numeric ranking.

## Approach 1: Using Case Statement

Let us first observe the code

```--Declare the Table Variable

DECLARE @T TABLE(

Id INT IDENTITY(1,1),

RangeDescription VARCHAR(20)

)

--Populate some values
INSERT INTO @T SELECT '200' UNION ALL
SELECT '45-90' UNION ALL
SELECT '190-200' UNION ALL
SELECT '70-100' UNION ALL
SELECT '30-50' UNION ALL
--SELECT '193-200' UNION ALL
--SELECT '189-200' UNION ALL
SELECT '50-30'

--Project the records
SELECT *
FROM @T
ORDER BY

CASE WHEN CAST(REPLACE(LEFT(RangeDescription,CHARINDEX('-',RangeDescription)),'-','') AS INT) = 0
THEN CAST(RangeDescription AS INT)
ELSE CAST(REPLACE(LEFT(RangeDescription,CHARINDEX('-',RangeDescription)),'-','')  AS INT)
END DESC
```

The statement REPLACE(LEFT(RangeDescription,CHARINDEX('-',RangeDescription)),'-',''), replaces the "-" with blank string.

```SELECT RangeDescription, Replace = REPLACE(LEFT(RangeDescription,CHARINDEX('-',RangeDescription)),'-','')
FROM @T
```

Then we are converting the value to Integer using the CAST function.

```SELECT
RangeDescription
, Replace = REPLACE(LEFT(RangeDescription,CHARINDEX('-',RangeDescription)),'-','')
,Cast = CAST(REPLACE(LEFT(RangeDescription,CHARINDEX('-',RangeDescription)),'-','') AS INT)
FROM @T
```

Now we have everything as numeric. And hence the sorting based on numeric ranking works.

## Approach 2: Using IIF Logical function

A similar logic is implemented by using the Denali's IIF Logical function as under.

```--Declare the Table Variable

DECLARE @T TABLE(

Id INT IDENTITY(1,1),

RangeDescription VARCHAR(20)

)

--Populate some values
INSERT INTO @T SELECT '200' UNION ALL
SELECT '45-90' UNION ALL
SELECT '190-200' UNION ALL
SELECT '70-100' UNION ALL
SELECT '30-50' UNION ALL
--SELECT '193-200' UNION ALL
--SELECT '189-200' UNION ALL
SELECT '50-30'

--Project the records
SELECT
*
FROM @T
ORDER BY
IIF(CAST(REPLACE(LEFT(RangeDescription,CHARINDEX('-',RangeDescription)),'-','') AS INT) = 0,
CAST(RangeDescription AS INT),
CAST(REPLACE(LEFT(RangeDescription,CHARINDEX('-',RangeDescription)),'-','')  AS INT)
) DESC
```

## Conclusion

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.