the order in which the IN clause values were specified. What I have is a
table that can be sorted on a number of columns yet I want to pull back a
subset of rows. I have the set of rows needed but I am unable to return the
rows in the correct order using the IN clause.
For example:
SELECT
T.ID,
T.Name
FROM
MyTable
WHERE
T.ID IN ('1,3,2')
Notice that the ID order is 1,3,2. I want the rows in that order without
having to use an Order By on the correct column. That would require that I
a) use dynamic SQL just to use the correct order by column or b) provide the
same query a bunch of times just changing the sorting. I would prefer to not
do either.
Is this possible in SQL Server?One option is to use a CASE expression like:
ORDER BY CASE id WHEN 1 THEN 1
WHEN 3 THEN 2
WHEN 2 THEN 3
END ;
For a general option, use CHARINDEX or PATINDEX function like:
ORDER BY CHARINDEX( ',' + @.list + ',', ',' + id + ',' ) ;
Anith|||Tim Menninger wrote:
> I am trying to make the IN clause of a stored procedure return the rows in
> the order in which the IN clause values were specified. What I have is a
> table that can be sorted on a number of columns yet I want to pull back a
> subset of rows. I have the set of rows needed but I am unable to return th
e
> rows in the correct order using the IN clause.
> For example:
> SELECT
> T.ID,
> T.Name
> FROM
> MyTable
> WHERE
> T.ID IN ('1,3,2')
> Notice that the ID order is 1,3,2. I want the rows in that order without
> having to use an Order By on the correct column. That would require that I
> a) use dynamic SQL just to use the correct order by column or b) provide t
he
> same query a bunch of times just changing the sorting. I would prefer to n
ot
> do either.
> Is this possible in SQL Server?
You should know that you cannot reliably order any query without using
ORDER BY. Try:
DECLARE @.in VARCHAR(100)
SET @.in = '1,3,2'
SELECT T.id, T.name
FROM MyTable
WHERE CHARINDEX(','+CAST(id AS VARCHAR)+',',','+@.in+',')>0
ORDER BY CHARINDEX(','+CAST(id AS VARCHAR)+',',','+@.in+',');
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I'd try to modify Erland Sommarskog's UDF iter_charlist_to_table that
parses a comma-separated string, found at
http://www.sommarskog.se/arrays-in-sql.html
CREATE FUNCTION iter_charlist_to_int_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
value int,
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos,
@.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (value, nstr) VALUES(cast(@.tmpval as int),
@.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(value, nstr) VALUES (cast(ltrim(rtrim(@.leftover)) as
int), ltrim(rtrim(@.leftover)))
RETURN
END
create table #t(i int)
insert into #t values(1)
insert into #t values(2)
insert into #t values(3)
insert into #t values(4)
insert into #t values(5)
select #t.i from #t, dbo.iter_charlist_to_int_table('1,3,2', ',') t
where #t.i=t.value
order by t.listpos
i
--
1
3
2
(3 row(s) affected)|||Your IN clause has only one member. You are confusing IN (1,3,2) and IN
('1,3,2'). You probably need to use dynamic SQL anyway to get your query
working the way you want it.
For example, with this:
create table fred
(
ID varchar(2),
Name varchar(100)
)
go
insert into fred(ID,Name)
select '1','Jim' union
select '2','Tom' union
select '3','Appleby'
select id,name from fred where id in ('1,2')
The select doesn't return anything. If ID were an int, you would get a
syntax error in the select statement
"Tim Menninger" <tmenninger@.comcast.net> wrote in message
news:e77bacLMGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am trying to make the IN clause of a stored procedure return the rows in
>the order in which the IN clause values were specified. What I have is a
>table that can be sorted on a number of columns yet I want to pull back a
>subset of rows. I have the set of rows needed but I am unable to return the
>rows in the correct order using the IN clause.
> For example:
> SELECT
> T.ID,
> T.Name
> FROM
> MyTable
> WHERE
> T.ID IN ('1,3,2')
> Notice that the ID order is 1,3,2. I want the rows in that order without
> having to use an Order By on the correct column. That would require that I
> a) use dynamic SQL just to use the correct order by column or b) provide
> the same query a bunch of times just changing the sorting. I would prefer
> to not do either.
> Is this possible in SQL Server?
>|||Someone was asleep in RDBMS 101 class! What is the definition of a
table? It models a set of rows. By definition a set has no ordering.
This is what you should have learned the first w
in class.Do this in the front end, where all formatting and presentation is done
in a tiered architecture (w
#2) or with an ORDER BY clause toconvert from a tale to a cursor.sql
rasesh_dave:
vantages of the above stored proc.