maintaining ordering from a select statement

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 2005 Forums

Transact-SQL (2005)

maintaining ordering from a select statement

Author

Topic

skuvnar
Starting Member

2 Posts

Posted - 2008-07-03 : 20:25:12

Hi guys,

I have a problem of maintaining ordering.

for example when I do:
SELECT * FROM users WHERE userID IN (3,1,4,2)

the result is (by default) ordered by the 'userID'
eg
---------
1 user1
2 user2
3 user3
4 user4
---------

but I want it to in the same order with the IN parameter
eg
---------
3 user3
1 user1
4 user4
2 user2
---------

I hope this is clear enough
Many thanks

dshelton
Yak Posting Veteran

73 Posts

Posted - 2008-07-03 : 22:05:40

SELECT * FROM users WHERE userID IN (3,1,4,2)
ORDER BY CASE WHEN userID = 3 THEN 1
WHEN userID = 1 THEN 2
WHEN userID = 4 THEN 3
WHEN userID = 2 THEN 4
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-03 : 23:13:53

[code]DECLARE @users TABLE
(
userID int,
userName varchar(5)
)
INSERT INTO @users
SELECT 1, 'user1' UNION ALL
SELECT 2, 'user2' UNION ALL
SELECT 3, 'user3' UNION ALL
SELECT 4, 'user4'

SELECT *
FROM @users u
WHERE u.userID IN (3, 1, 4, 2)
/*
userID userName
----------- --------
1 user1
2 user2
3 user3
4 user4

(4 row(s) affected)
*/

SELECT u.*
FROM @users u
INNER JOIN dbo.fnParseList(',', '3, 1, 4, 2') c
ON u.userID = c.Data
ORDER BY c.RowID

/*
userID userName
----------- --------
3 user3
1 user1
4 user4
2 user2

(4 row(s) affected)
*/[/code]

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

skuvnar
Starting Member

2 Posts

Posted - 2008-07-05 : 22:03:13

Ok thanks alot guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources