SELECT In order by specific Ids

I've got a bit of a strange question, a short version of the sql I'm using is

SELECT * FROM table WHERE id IN (5,10,1,9)

Is there a way to get the results in the same order as the IN clause? so the results should be ordered by the id and be returned as 5,10,1,9

Maybe im not thinking about it the right way but its ran from within a vb.net app and the IN(x) is generated from somewhere else.

On a side note is it better performance wise to use IN OR Where id= 5 AND id = 10 etc, sometimes its up to about 20 ids.

Thanks :)

re: SELECT In order by specific Ids

If the list inside IN is made up of constants and not a subquery, IN() and OR are the same. IN is actually a simplified OR and will execute the same way. For faster execution, create an index for ID and place the most probable value first. The order of the constant on your list does not affect the way the rows are ordered.

If the list is coming form a query, it would be better to use JOIN or EXISTS. In your case, since you need a different sorting, a JOIN might be better. You can include an extra column on the ORDER BY clause even if it's not on your SELECT list.

If it's a list of constant and you still want it ordered that way, you will need to resort to CASE..WHEN..END function. Something like:

Here's the catch. If that list is dynamic, your ORDER BY should be dynamic as well. Hence you might want to consider using a dynamic sql statement instead.

-- CK

re: SELECT In order by specific Ids

The list of Ids is dynamic, its for a vb net app, the Ids can be selected by the user in an order or it might be from a different select statment, this particular case is ordered by a date but the ids are passed from a different function.

I think i'll just generate the code you posted for the ids as the ids are in a collection so its simple enough to do. Will it be a problem with performance if i use that for about 20+ ids?

Never knew you could do that :)

re: SELECT In order by specific Ids

The choice between IN and multiple ORs are ignorable. They act the same. Since you don't know which ID the user will choose first, you will not be able to arrange the content of your list.

Just a reminder, if there's a NULL value in anywhere on the list, NOT IN (just in case you'll use it) will not return any rows. So if the list is coming from a subquery and it did not return any row, your entire query will not return any row at all.

Happy coding.

-- CK