Custom sorting with ORDER BY AND IIF

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 437
Suppose we have a table as under

DECLARE @T TABLE(COLOR VARCHAR(10))
INSERT INTO @T
SELECT 'RED' UNION ALL
SELECT 'BLUE' UNION ALL
SELECT 'GREEN' UNION ALL
SELECT 'ORANGE'

SELECT
COLOR
FROM @T

/*

COLOR
--------
RED
BLUE
GREEN
ORANGE

*/

Now suppose we want to have Green to appear first followed by Blue then Red and finally Orange.

The below query will help to do so

SELECT 
COLOR
,Rn=ROW_NUMBER() OVER(ORDER BY IIF(COLOR ='BLUE',0,IIF(COLOR ='GREEN',1, IIF(COLOR ='RED',2,3))))
FROM @T


/*

COLOR	Rn
-------- -----
BLUE 1
GREEN 2
RED 3
ORANGE 4

*/

Comments or Responses

Login to post response