How to disable the defaut order in "IN" search? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to disable the defaut order in "IN" search?

Author

Topic

donar
Starting Member

22 Posts

Posted - 2006-02-21 : 10:04:18

Hi guys,

Here is the question:

select key from tableone where key in (5, 89, 3, 21, 2,6)

the result always be:
2
3
5
6
21
89

since the key is the primary key in tableone.

How can I get the query result the same sequence I indicated in the IN, I want the result be:
5
89
3
21
2
6

Please help! Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-21 : 10:14:52

This may not be effecient

select key from tableone where key in (5, 89, 3, 21, 2,6)
order by Case when key=5 then 1 when key=89 then 2 ... when key=6 then 6 end

Madhivanan

Failing to plan is Planning to fail

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-21 : 10:55:51

this is coming back index ordered because of the PK. you can never guarantee the order of the data in a resultset unless you use an ORDER BY clause In this case the ORDER BY clause does you no good, unless there is another key that you can return which will give you the order you are looking for.

All that aside, this is probably something best handled in the presentation layer of your application instead of at the database layer anyway.

-ec

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-22 : 07:37:49

You can insert values from in list into table variable one at time at the order you need. Table variable would conist of identity column and column where value from in-list is stored. After that you can join table variable with original table and order by identity column. I agree that it is probably better idea to handle ordering on the client.

Kristen
Test

22859 Posts

Posted - 2006-02-22 : 08:13:24

"one at a time"

If you used a CSV Split Function, to get the number list into a temporary table, then that [temporary] table can also have an Identity column, which you could use in the ORDER BY.

Using my function from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648 as an example:


select T1.key 
from tableone AS T1
    JOIN dbo.KK_FN_DelimSplit_V2('5, 89, 3, 21, 2,6', ',', NULL) AS L
      ON L.value = T1.key
ORDER BY L.Item

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources