How to save order of items when select by id?

I have some query of ids such as ids={1000,50,300,40} I whant to select some items with that ids from db table and I want to save the sequence of items accordind to the sequence of ids. I mean, i use select statment:

SELECT * 
  FROM items 
 WHERE id IN (1000,50,300,40)

And I would like to get result as

id      name
-------------
1000   item1
50     item2
300    item3
40     item4

But SQL returns result as

id      name 
-------------
40     item4 
50     item2 
300    item3 
1000   item1

It is very important to s开发者_StackOverflowave the order of ids. Can anybody help me?

Assuming SQL Server 2000+, use a CASE expression:

  SELECT i.* 
    FROM ITEMS i
   WHERE i.id IN (1000,50,300,40)
ORDER BY CASE i.id
           WHEN 1000 THEN 1
           WHEN 50 THEN 2
           WHEN 300 THEN 3
           WHEN 40 THEN 4
           ELSE 5
         END 

You also have the issue of how to parameterise the in clause. If you are on SQL Server 2008 you can use Table Valued Parameters. You could use a 2 column TVP with both an order by column and a value column then join onto that.

For previous versions you can use a split Table Valued Function that returns 2 columns (index and value) and join onto that.

SELECT i.*    
FROM dbo.split('1000,50,300,40') s
JOIN items i ON i.id = s.value
ORDER BY s.idx

An example of such a function is here. I am in no way endorsing that particular implementation as there are probably far better ones about but you can use it to see the general approach.

Only thing I can think of is to develop or discover some other column that will return objects in the proper order, or UNION a series of queries by individual ID which will cause each query result to be appended, in order, to all the previous result sets.

The reason this is happening is that the SQL engine is performing an index or table scan to find the records, and as records are generally stored in order of their IDs, that's the default order it will find them and add them to the result set.

use

select * from items WHERE id IN (1000,50,300,40) order by id desc

Add an ORDER BY clause to the query with ASC or DESC for ascending or descending order respectively.

select * from items where id in (1000,50,300,40) order by id