468,316 Members | 2,065 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

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 :)
Oct 1 '08 #1
6 16308
ck9663
2,878 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. ORDER BY
  2. CASE 
  3. WHEN ID = 5 then 1
  4. WHEN ID = 10 then 2
  5. WHEN ID = 1 then 3
  6. WHEN ID = then 4
  7. else 5
  8. 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
Oct 1 '08 #2
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 :)
Oct 1 '08 #3
ck9663
2,878 Expert 2GB
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
Oct 1 '08 #4
Hi AlmightyJu,

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

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tmptbl
  3. WHERE id IN (5,10,14,1)
  4. 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".
Aug 24 '10 #5
NeoPa
32,075 Expert Mod 16PB
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.
Aug 25 '10 #6
ck9663
2,878 Expert 2GB
And placing conversion function in WHERE or ORDER BY clause could potentially slow your query...

Good Luck!!!

~~ CK
Aug 25 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

23 posts views Thread by ian justice | last post: by
18 posts views Thread by ian justice | last post: by
2 posts views Thread by Paulo Andre Ortega Ribeiro | last post: by
3 posts views Thread by Tcs | last post: by
7 posts views Thread by Lauren Quantrell | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.