query for IN operator returned in order specified

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

General SQL Server Forums

New to SQL Server Programming

query for IN operator returned in order specified

Author

Topic

joejnknsn
Starting Member

3 Posts

Posted - 2009-11-04 : 20:39:19

If I have a where clause like this:

WHERE ID IN (5, 2, 6, 3)

I want the result to be returned in the order specified.

So the array would be like this Array(5, 2, 6, 3). By default it returns the result in the order that is in the database which is (2, 3, 5, 6). I found a way to do this and then I lost the page and forgot my google search that found the solution.

herchoicejewelry.com

nalnait
Starting Member

14 Posts

Posted - 2009-11-04 : 20:54:09

HI,joejnknsn

you can try it use 'ORDER BY CASE WHEN id=5 THEN 1 WHEN id=2 THEN 2.....END'
hope it's helpful

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-04 : 20:59:34

[code]
declare @IDs table
(
seq int identity(1,1),
ID int
)

insert into @IDs (ID)
select 5 union all
select 2 union all
select 6 union all
select 3

SELECT *
FROM yourtable t INNER JOIN @IDs i ON t.ID = i.ID
ORDER BY i.seq
[/code]

OR
[code]
SELECT *
FROM yourtable t INNER JOIN fnParseList(',', '5,2,6,3') i ON t.ID = i.Data
ORDER BY i.RowID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

joejnknsn
Starting Member

3 Posts

Posted - 2009-11-04 : 23:09:48

Here is the error I got when trying Khtan's second option

Error

SQL query: Documentation

SELECT *
FROM residence r
INNER JOIN fnParseList(
',', '9,2,5,3'
)i ON r.ID = i.Data
ORDER BY i.RowID
LIMIT 0 , 30

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ',' , '9,2,5,3' ) i ON r . ID = i . Data ORDER BY i . RowID
LIMIT 0, 30' at line 1

quote:

Originally posted by khtan


declare @IDs table
(
    seq int identity(1,1),
    ID  int
)

insert into @IDs (ID)
select 5 union all
select 2 union all
select 6 union all
select 3

SELECT *
FROM   yourtable t INNER JOIN @IDs i ON t.ID = i.ID
ORDER BY i.seq


SELECT *
FROM   yourtable t INNER JOIN fnParseList(',', '5,2,6,3') i ON t.ID = i.Data
ORDER BY i.RowID

KH
[spoiler]Time is always against us[/spoiler]

herchoicejewelry.com

joejnknsn
Starting Member

3 Posts

Posted - 2009-11-04 : 23:14:55

I found the answer very simply

Do your query WHERE clause in any order, then order it how you want in the ORDER BY FIELD clause.

SELECT * FROM table
WHERE ID IN (19, 40, 50, 51)
ORDER BY FIELD (ID, 51, 19, 40, 50)

herchoicejewelry.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-05 : 00:51:16

you are using MYSQL and this is a MS SQL Server forum

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources