Managing Order By - SQL Server Forums

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

Transact-SQL (2000)

Managing Order By

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-07 : 08:42:59

Dave writes "I have a query where that contains a "where in" clause. Is it possible to have the results returned in the same order as the list in the "Where In" clause?

Example:

select id, title
from mytable
where id in (7,4,9,3,5)
order by id

How can I get the titles returned in the same order as the list of id's in the "where in"? (7,4,9,3,5)

Thanks,
Dave"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-07 : 09:21:13

one way is to create a temp table variable that holds your desired order in the identity column named CustomOrderBy.
you join that temp table to your mytable on the id column and you do Order By CustomOrderBy

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-07 : 09:28:24

[code]
select id, title
from mytable
where id in (7,4,9,3,5)
order by case id
when 7 then 1
when 4 then 2
when 9 then 3
when 3 then 4
when 5 then 5
end

[/code]

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-03-07 : 21:47:30

select id, title from mytable
inner join
(
select 7 id, 1 sortorder union all
select 4 , 2 union all
select 9 , 3
) x on mytable.id=x.id
order by sortorder

Keeps your stuff in one place.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources