472,794 Members | 1,930 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,794 software developers and data experts.

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 17445
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
  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
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
32,534 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
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

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

Similar topics

by: ian justice | last post by:
Before i post actual code, as i need a speedyish reply. Can i first ask if anyone knows off the top of their head, if there is a likely obvious cause to the following problem. For the moment i've...
by: ian justice | last post by:
Before i post actual code, as i need a speedyish reply. Can i first ask if anyone knows off the top of their head, if there is a likely obvious cause to the following problem. For the moment i've...
by: Adam Nemitoff | last post by:
Is is possible to construct a SELECT statement that contains a WHERE clause that uses the value from a column in the "next" row? ie. given a table with a single field named "myField" with the...
by: Stefan Engstr?m | last post by:
My mysql is responding strangely to a select when I try to ask for particular columns instead of everything. Here is an example which is querying a database of a few thousand elements in each...
by: Paulo Andre Ortega Ribeiro | last post by:
I have a Microsoft SQL Server 7.0. I wrote a sql command that creates a temporary table with a ORDER BY clause. When a execute a SELECT on this temporary table sometimes the result is ok, but...
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
by: Lauren Quantrell | last post by:
Is there any speed/resource advantage/disadvantage in using Select Case x Case 1 Case 2 etc. many more cases... End Select VS.
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without...
by: Mahesh S | last post by:
Hi I would like to write a SQL select statement that would fetch rows numbered 50 to 100. Let me clarify, if i say "fetch first 10 rows only", it returns the first 10 rows from the resultset....
by: tjc0ol | last post by:
Hi guys, I'm a newbie in php and I got error in my index.php which is: 1054 - Unknown column 'p.products_id' in 'on clause' select p.products_image, pd.products_name, p.products_id,...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.