How to query in the given order

Question

select * from table where options=(10,223,43,1,23,54,323,32)

But the result is not coming in the given order of options. Options need to be changed frequently.

Answer 1

First, you need to use the IN operator with multiple values. WHERE options IN (10,223,43,1,23,54,323,32) Secondly, relational databases are agnostic about row order and can return them in any order they want, which can change with different execution plans. To force them to return in a particular order that's meaningful to you, add the ORDER BY clause:

SELECT * FROM table WHERE options IN (10,223,43,1,23,54,323,32) ORDER BY options

Will return them in numerical order of the options value. If instead you want them ordered in some other way, such as the order in which you have shown your IN clause, you need to do a bit of magic with the DECODE to construct an ascending pseudo-value by which to order the results:

SELECT * 
  FROM table 
 WHERE options IN (10,223,43,1,23,54,323,32) 
 ORDER BY DECODE(options,10,1,
                         223,2,
                         43,3,
                         23,4,
                         54,5,
                         323,6,
                         32,7)

You could get away with listing them only once, but it would not perform well on a large table because it wouldn't be able to use indexes:

SELECT *
  FROM (SELECT x.*,
               DECODE(options,10,1,
                              223,2,
                              43,3,
                              23,4,
                              54,5,
                              323,6,
                              32,7) seq
          FROM table x)
 WHERE seq > 0
 ORDER BY seq

Be careful, though, if this is too be used with high frequency and a variable number of options, as that will create a lot of unsharable cursors, even with bind variables, due to the varying size of the IN and DECODE argument list. If this will be used with high frequency, I would instead recommend inserting the options and order sequence into a temp table and then selecting from it and joining to your real table. That way you don't have to list any options in your SQL at all.