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
*/