SQL order by elements from IN clause

Question

I have an ITEM table and I'd like to return results ordered by the same order informed in a IN clause. These IDs are informed by the user.

Today I have this:

SELECT * 
FROM ITEM
WHERE ITEM_ID IN (45,2,671,6)
ORDER BY CASE ITEM_ID 
  WHEN 45 THEN 0
  WHEN 2 THEN 1
  WHEN 671 THEN 2
  WHEN 6 THEN 3
END

This works but CASE has a limit of 65535 arguments which is giving me ORA-00939: too many arguments for function when I need more than that.

Is there an alternative solution for Oracle, without limits and preferably with good performance and accepted in other DBMS as well?

Thanks

Answer 1

Where are the elements coming from? I would suggest that you use a join:

SELECT i.* 
FROM ITEM i JOIN
     (SELECT 45 as id, 1 as ord FROM DUAL UNION ALL
      SELECT 2 as id, 2 as ord FROM DUAL UNION ALL
      SELECT 671 as id, 3 as ord FROM DUAL UNION ALL
      SELECT 6 as id, 4 as ord FROM DUAL
     ) x
     ON i.ITEM_ID = x.id
ORDER BY x.ord;

This is simpler if the ids come from a table with the relevant information.

Answer 2

While your logic and above solutions are good for small scale, if you are talking about more than 65000 items, you need a solution which is scalable.

My suggestion is to split this task to 2 steps.

Step 1

Create a temporary table, This temporary table will have 3 columns minumum

TEMP_ITEM_ORDER_TABLE (
  session_key varchar2(50),
  item_id number,
  item_report_order number
)

Each time user orders such a query, insert data ,i.e item ids and their sequence no into this temporary table with some unique key to identify user session (possibly user id or session id). This trick is to avoid collision of item lists when multiple users simultaneously fire reports.

Step 2

Now fire your report query joining your main table, temp table with session_key. In the query order data based on your input order (stored in temp table already)

SELECT 
  T1.* , T2.item_report_order
FROM ITEM T1, TEMP_ITEM_ORDER_TABLE T2
  WHERE T1.ITEM_ID = T2.ITEM_ID
  AND T2.session_key = :input_session_key
  ORDER BY t2.item_report_order

This method is

1. database agnostic
2. scalable with any number of inputs
3. Gives best possible performance

Note: To further improve query performance, create index on session_key, item_id in temp table also create index on item_id on ITEM table (if not exists already)

Edit: Oracle offers Global Temporary Table feature, which creates has features to allow records only with in session and auto clean up upon commit/end of session etc. You can make use of this feature and avoid session key, but this solution can not be replicated on other database products unless they support similar feature.

Answer 3

You do not need to create a temporary table, instead you can use a collection:

SELECT i.*
FROM   ITEM i
       INNER JOIN
       (
         SELECT ROWNUM AS rn,
                COLUMN_VALUE AS value
         FROM   TABLE( SYS.ODCINUMBERLIST( 45, 2, 671, 6 ) )
       ) v
       ON ( i.ITEM_ID = v.VALUE )
ORDER BY v.RN;

It can even be passed in as a bind variable:

SELECT i.*
FROM   ITEM i
       INNER JOIN
       (
         SELECT ROWNUM AS rn,
                COLUMN_VALUE AS value
         FROM   TABLE( :your_array )
       ) v
       ON ( i.ITEM_ID = v.VALUE )
ORDER BY v.RN;