Ordering by the Order of Values in a SQL In() Clause

Ordering by the order of values in a SQL IN() clause

Use MySQL's FIELD() function:

SELECT name, description, ...
FROM ...
WHERE id IN([ids, any order])
ORDER BY FIELD(id, [ids in order])

FIELD() will return the index of the first parameter that is equal to the first parameter (other than the first parameter itself).

FIELD('a', 'a', 'b', 'c')

will return 1

FIELD('a', 'c', 'b', 'a')

will return 3

This will do exactly what you want if you paste the ids into the IN() clause and the FIELD() function in the same order.

Keep order from 'IN' clause

There will be no reliable ordering unless you use an ORDER BY clause ..

SELECT SomeField,OtherField
FROM TestResult 
WHERE TestResult.SomeField IN (45,2,445,12,789)
order by case TestResult.SomeField
         when 45 then 1
         when 2  then 2
         when 445 then 3
         ...
         end

You could split the query into 5 queries union all'd together though ...

SELECT SomeField,OtherField
FROM TestResult 
WHERE TestResult.SomeField = 4
union all
SELECT SomeField,OtherField
FROM TestResult 
WHERE TestResult.SomeField = 2
union all
...

I'd trust the former method more, and it would probably perform much better.

Select records with order of IN clause

You have a couple of options. Simplest may be to put the IN parameters (they are parameters, right) in a separate table in the order you receive them, and ORDER BY that table.

Ordering SQL Server results by IN clause

How are you parameterising the IN clause?

As you are on SQL Server 2008 I would pass in a Table Valued Parameter with two columns item and sort_order and join on that instead. Then you can just add an ORDER BY sort_order onto the end.

SQL Server Select query with IN() and order by the same

In SQL-Server, when you want to order by something, you have to specifically spell it out.

Try this

select * from table where column_id IN (5,64,2,8,7,1)
order by
case column_id
    when 5 then 1
    when 64 then 2
    when 2 then 3
    when 8 then 4
    when 7 then 5
    when 1 then 6
    else 10
end;

Sort by order of values in a select statement in clause in mysql

Actually, this is better:

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIELD(id,5,2,6,8,12,1);

heres the FIELD documentation:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field

SQL order by elements from IN clause

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.

---

Related Topics