SELECT In order by specific Ids

[AlmightyJu]

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 :)

[ck9663]

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:

ORDER BY
CASE 
WHEN ID = 5 then 1
WHEN ID = 10 then 2
WHEN ID = 1 then 3
WHEN ID = then 4
else 5
END

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

[AlmightyJu]

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 :)

[ck9663]

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

[Paul Klinkenberg]

The answer

Hi AlmightyJu,

About 2 years later, I am seeing your question because I was looking for the same answer.
The answer is:

SELECT *
FROM tmptbl
WHERE id IN (5,10,14,1)
ORDER BY CHARINDEX(','+CONVERT(varchar, tmptbl.id)+',', ',5,10,14,1,')

You probably notice the extra commas (,) in the charindex function. Without those, the id "1" would become the 2nd or 3rd row, because it would then match the "1" of "10".

[NeoPa]

I think you mean an answer Paul. Clever as that answer is, the previously provided answer works perfectly well too. I like yours, and it may even be easier to code up, but that's as far as one could go.

[ck9663]

And placing conversion function in WHERE or ORDER BY clause could potentially slow your query...

Good Luck!!!

~~ CK