sorting sql query results in various ways depending on data value

Question

Given the following results from a sql query:

1,
4,
Red,
Blue,
3,
Green,
2,
Yellow,
Magneta,
Rubi

The right order I am trying to achieve is:

Red,Blue,Green, Magneta,Rubi, Yellow, 1, 2, 3, 4

• Red, Blue and Green must always be at the top and in that order
• Then, the alpha items should follow order asc (M,R,Y)
• Follow by the numeric items in ascending order to ( 1, 2, 3...)

I have been playing around with the following to no avail, so if anybody can point me in the right direction

SELECT myCol FROM myTable
ORDER BY
CASE WHEN myCol='Red' then 1
     WHEN myCol='Blue' then 2
     WHEN myCol='Green' then 3 END,
CASE
     WHEN myCol not in ('Red','Blue','Green') and IsNumeric(party_id) = 0 then myCol END ASC,
CASE
     WHEN myCol not in ('Red','Blue','Green') and IsNumeric(party_id) = 1 then CAST(myCol AS INT) END ASC

Thanks!

EDIT 1: My current order is actually returning:

1,2,3,4, Magneta, Rubi, Yellow, Red, Blue, Green

Answer 1

You could try this

SELECT myCol 
FROM myTable
ORDER BY
    CASE WHEN myCol='Red' THEN 1
        WHEN myCol='Blue' THEN 2
        WHEN myCol='Green' THEN 3 
        ELSE 4
    END,
    CASE
        WHEN myCol NOT IN ('Red','Blue','Green') AND IsNumeric(party_id) = 1 
            THEN CAST(myCol AS INT) 
        ELSE -1 
    END,
    CASE
        WHEN myCol NOT IN ('Red','Blue','Green') AND IsNumeric(party_id) = 0 
            THEN myCol 
    END;