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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityFirst, 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.