Ordering by the order of values in a SQL IN() clause
SELECT name, description, ...
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 ..
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
You could split the query into 5 queries union all'd together though ...
WHERE TestResult.SomeField = 4
WHERE TestResult.SomeField = 2
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
As you are on SQL Server 2008 I would pass in a Table Valued Parameter with two columns
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.
select * from table where column_id IN (5,64,2,8,7,1)
when 5 then 1
when 64 then 2
when 2 then 3
when 8 then 4
when 7 then 5
when 1 then 6
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:
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.
Create a temporary table,
This temporary table will have 3 columns minumum
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.
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)
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
- database agnostic
- scalable with any number of inputs
- 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)
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.