Sort records based on Numeric Ranking in SQL Server

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

In this article we have learnt about numeric sorting. Hope this will be helpful. Thanks for reading. Zipped file attached.