Order .. - 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

General SQL Server Forums

New to SQL Server Programming

Order ..

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-02-26 : 10:44:10

Hi I want the order of this data to come back in the same
order of the fields in the IN clause
[CODE]
select name,code from TabA
where name in
(
'text1' ,
'text2'
'more text'
.
.
.
)

[/CODE]

Is there a way of doing this ? it will save a whole lot of time ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-26 : 10:50:23

[code]declare @t table
(
[id] int identity(1,1),
[name] varchar(50)
)

insert into @t
select 'text1' union all
select 'text2' union all
select 'more text' union all
...

select name,code
from TabA t1 join @t t2 on t1.[name] = t2.[name]
order by t2.[id][/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 12:07:29

or:-

select name,code,
case when name='text1' then 1
     when name='text2' then 2
.......
end as SortOrder
 from TabA
where name in
(
'text1'  ,              
'text2' 
'more text'        
.
.
.
)

Order by SortOrder

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-26 : 17:46:03

Here's a variation on Harsh's
select
name,code
from tabA join
(
select 1 id, 'text1' name union all
select 2, 'text2' union all
select 3, 'more text'
) tabB
on tabA.[name] = tabB.[name]
order by tabB.[id]

Be careful: Saket's solution won't work because there is no order by.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources