Prashobh Chandran Prashobh Chandran - 4 months ago 17
SQL Question

Oracle have any equivalent function for mysql "FIELD"

This is mysql query. How i can run this query in oracle,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)
Comments