Sort A SQL Server Table With CASE Or CHARINDEX

Let's say you have a table with states in your database, you only have 3 values NY, ME and SC.
You want to order the result like this: first NY followed by SC and ME last.
You can do that in two different ways
One: use a case statement in your order by
Two: use Charindex in your order by
Let's see how that works

CREATE TABLE #TEST (
STATE CHAR(2))

INSERT INTO #TEST
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC'

-- order by using CASE

SELECT *
FROM #TEST
ORDER BY CASE STATE
WHEN 'NY' THEN 1
WHEN 'SC' THEN 2
ELSE 3
END

--Order by using CHARINDEX
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'NY-SC-ME')

--or without NY since CHARINDEX will return 0 for NY and it will be first
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-ME')


--the problem is of course if you have more values and you only want to have NY and SC showing up first and second
--let's insert 2 more rows
INSERT INTO #TEST
SELECT 'IL'
UNION ALL
SELECT 'CA'

-- Now the CHARINDEX Order doesn't work
-- the trick is to make it Descending and switch the states around
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-NY') DESC

or this way

--Order by using CHARINDEX DESC
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'ME-SC-NY') DESC