CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Vamsi
Vamsi

Reputation: 873

Order by where condition

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.

Upvotes: 2

Views: 492

Answers (4)

user1684833
user1684833

Reputation: 

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

and so on....

Upvotes: 3

John Woo
John Woo

Reputation: 263703

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');

Upvotes: 6

user1726343
user1726343

Reputation:

You can combine ORDER BY with a CASE WHEN construct:

ORDER BY CASE 
            WHEN state = 'NC' THEN 1
            WHEN state = 'North Carolina' THEN 2
            ...
         END

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

order by case when `state`='NC' or `state`='North Carolina' then 1 
              when `state`='TN' or `state`='Tennessee' then 2
              when `state`='CO' or `state`='Colorado' then 3
         end

Upvotes: 1

Related Questions