ORDER BY clause to sort values in an explicit order in CodeIgniter

Question

I have the following query:

select `state` from `table1` 
where `state` in ('NC','North Carolina','TN','Tennessee','CO','Colorado','NM','New Mexico','UT','Utah')
limit 200 offset 0

I need the results ordered by state as they appear in my where clause (i.e. first NC, then TN, then CO, and so on).

How can I do this? I tried using order by but it doesn't give this result.

Answer 1

you can use ORDER BY FIELD

SELECT `state`
FROM `table1`
WHERE `state` IN ('NC','North Carolina','TN','Tennessee','CO','Colorado','NM','New Mexico','UT','Utah')
ORDER BY FIELD(`state`, 'NC','North Carolina','TN','Tennessee','CO','Colorado','NM','New Mexico','UT','Utah');

• SQLFiddle Demo