Need result set of sql Query in the order gave as input in "in" function without any creation and insertions

Question

Need the result set of the SQL query in the order of the param values what i gave in the in function order.

mysql> select * from state where state_name in ("UP","AP","Orissa","MP");

+------------+
| state_name |
+------------+
| AP         |
| MP         |
| UP         |
| Orissa     |
+------------+

4 rows in set (0.00 sec)

Please suggest a solution that can help without using any extra insertions and creations.

Answer 1

select * from state where state_name in ("UP","AP","Orissa","MP")    
order by FIELD(state_name ,"UP","AP","Orissa","MP")

Answer 2

With ANSI sql CASE

select * from state where state_name in ("UP","AP","Orissa","MP")
order by case state_name 
         when "UP" then 1 
         when "AP" then 2 
         when "Orissa" then 3 
         when "MP" then 4 
         else 5 end;

Answer 3

Old school:

SELECT
  State.*
FROM State
JOIN (SELECT 'UP' sn, '1' pos UNION ALL
      SELECT 'AP', '2' UNION ALL
      SELECT 'Orissa', '3' UNION ALL
      SELECT 'MP', '4') SortOrder
ON State.state_name = SortOrder.sn
ORDER BY SortOrder.pos;

SQL Fiddle