473,320 Members | 1,883 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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 17606
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,556 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

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

Similar topics

23
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...
18
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...
8
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...
0
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...
2
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...
3
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...
7
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.
19
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...
5
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....
4
tjc0ol
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,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.