Prashobh Chandran Prashobh Chandran - 4 months ago 12
MySQL Question

Does Oracle have any equivalent function for mysql "FIELD"

This is my MySQL query. How can I run this query in Oracle? Right now it's not working.

SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4);

Answer

Here is a stupid solution: (:-D)

SELECT * FROM mytable WHERE id IN (1,2,3,4)
ORDER BY CASE id WHEN 3 THEN 1
                 WHEN 2 THEN 2
                 WHEN 1 THEN 3
                 WHEN 4 THEN 4 END;

Or use DECODE function:

SELECT * FROM mytable WHERE id IN (1,2,3,4)
ORDER BY DECODE(id, 3, 1, 2, 2, 1, 3, 4, 4, 0)