[sql2005] order by defined value

[jlbantang]

hi guys, how would you sort columns according to defined values. suppose

action
-----------
MSC
GP
DL
IDA


output
GP
DL
MSC
IDA

i was thinking something like this:

select action from table1
order by 'gp','dl','msc','ida'

but im getting error

[si_the_geek]

Create another table containing two fields, the value you want to sort on ('gp', 'dl', etc), and a number representing the order it should be in (1,2, etc). The numbers do not need to be continuous (so you could have 50, 1000, etc), which might simplify things if you need to add new values later.

You can then Left-Join to the other table (so that other values get displayed), and sort by the numeric field.