November 1, 2007 at 4:13 am
My query is
select * from Items where ItemId in
(4354,14759 ,62990,105170,105244,14741,58495,14742,49798,14743,58496,14744,71631)
I'm getting the resultant set in the random order.. like this
4354
14741
14742
14743
14744
14759
49798
58495
58496
62990
71631
105170
105244
How to retrive as same as i have given inside IN clause
i.e --> In this order 4354,14759 ,62990,105170,105244,14741,58495,14742,49798,14743,58496,14744,71631
Any help would be very uself.
Thanks in Advance
Vid
November 1, 2007 at 5:07 am
The "in" expression is converted into an OR, and there is no guarantee for the order in which the individual parts evaluate. However, if the order in which the items are returned is really so important, you could create a lookup table that contains the values and their order. Then joining against this table and ordering on their order fields should work. If the items in the "in" expression are fixed, you could even use ti to do the filtering itself, or you could add extra columns to the lookup table to "store" the most frequent sets of items to look up.
Regards,
Andras
November 1, 2007 at 6:26 am
Andras is correct. You can't order by the order in an IN clause. You can order by column ordinal positions, but since these are row results, that doesn't help you out.
The only other method I can think of is to follow Andras's advice. Create a temp table to stick the values in the way you currently have them listed in the IN clause and an identity field that you could use in your ORDER BY clause.
November 2, 2007 at 7:31 am
If you want to change how records are ordered, you have to use the "Order By" clause and you have to meet 2 criteria.
1. You must use a field in the table. Constants alone won't work.
2. You need to force a TRUE test for each ordered item and then order those tests, like so:
select itemid from items
where itemid in (4354,14759 ,62990,105170,105244,14741,58495,14742,49798,14743,58496,14744,71631)
order by
case
when itemid = 4354 then 1 -- when itemid = 4354 it assumes order 1
when itemid = 14759 then 2 -- when itemid = 14759 it assumes order 2
when itemid = 62990 then 3 -- and so on
when itemid = 105170 then 4
when itemid = 105244 then 5
when itemid = 14741 then 6
when itemid = 58495 then 7
when itemid = 14742 then 8
when itemid = 49798 then 9
when itemid = 14743 then 10
when itemid = 58496 then 11
when itemid = 14744 then 12
when itemid = 71631 then 13
end
November 2, 2007 at 2:46 pm
I have used the following function for managing delimited strings in a list. It uses a Numbers table
to return the string as a table.
Do a search for Numbers table for more info on it. The concept has been written about a number of times,
and I used their idea. The numbers table I use is a table of all integer values 1-8000.
-- Numbers Table DDL
CREATE TABLE [dbo].[Numbers](
[Number] [int] NOT NULL,
CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED
(
[Number] ASC
)
-- udf_StringSplit function Definition
CREATE FUNCTION [dbo].[udf_StringSplit]
(@String VARCHAR(8000)
, @Delimiter char(1) = ','
)
RETURNS @return TABLE (ID INT IDENTITY(1,1), String VARCHAR(100))
AS
BEGIN
INSERT INTO @return (String) --RETURN
(
SELECT
SUBSTRING(@String,
CASE Number
WHEN 1 THEN 1
ELSE Number + 1
END,
CASE CHARINDEX(@Delimiter, @String, Number + 1)
WHEN 0 THEN LEN(@String) - Number + 1
ELSE CHARINDEX(@Delimiter, @String, Number + 1) - Number -
CASE
WHEN Number > 1 THEN 1
ELSE 0
END
END
) AS String
FROM dbo.Numbers
WHERE Number <= LEN(@String)
AND (SUBSTRING(@String, Number, 1) = @Delimiter
OR Number = 1)
)
RETURN
END
Something similar to the following might accomplish what you are after using this function.
DECLARE @list varchar(8000)
SET @list = '4354,14759 ,62990,105170,105244,14741,58495,14742,49798,14743,58496,14744,71631'
select i.* from Items i
INNER JOIN dbo.udf_StringSplit(@list, ',') s
ON i.ItemID=CAST(s.String as int)
ORDER BY s.ID
Donnie Sawford
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply