Possible Duplicates:
Ordering MySQL results by IN sequence?
Ordering by the order of values in a SQL IN() clause
i have the following table called "Products"
id desc
1 BL10
2 BL15
3 BL45
4 BL50
well, this is my query SELECT * FROM Products WHERE id IN(3,1,4,2)
I want it shows in the same order of clause IN, i.e. 3,1,4,2 :
id desc
3 BL45
1 BL10
4 BL50
2 BL15
but when i execute it, it shows ordered, How can i get that? I'm using MSSQL 2005
SELECT * FROM Products WHERE id IN (3, 1, 4, 2)
ORDER BY FIELD (id, 3, 1, 4, 2)
Your IN clause won't sort your result set, but instead your results will be returned in the order they are encountered in the query.
If you wanted, you could create an explicit ORDER BY to sort it in the order you want:
SELECT *
FROM Products
WHERE id IN (3,1,4,2)
ORDER BY (CASE WHEN id = 3 THEN 0
WHEN id = 1 THEN 1
WHEN id = 4 THEN 2
WHEN id = 2 THEN 3 END)
(This is DBMS-nonspecific, with the caveat that it'd be a pain in the ass to write if you have more values that you want in a specific order)
If the sort order is not dynamic but is intrinsic to the product, you probably need another field in your table for SortOrder that you can use in the ORDER BY clause:
SELECT * FROM Products WHERE id IN(3,1,4,2) ORDER BY SortOrder
Where SortOrder looks like this:
Id SortOrder
1 20
2 40
3 10
4 30
For SQL Server
SELECT number, type, low, high, status
FROM master.dbo.spt_values
JOIN [dbo].[fnSplit]('200,900,300',',') s ON number= CAST(s.value AS INT)
ORDER BY s.idx
Returns
number type low high status
----------- ---- ----------- ----------- -----------
200 P 26 1 0
900 P 113 16 0
300 P 38 16 0
This relies on a function which I didn't write. I'll try and find the original source. (Edit Maybe here?)
CREATE FUNCTION [dbo].[fnSplit]
(
@sText VARCHAR(8000),
@sDelim VARCHAR(20) = ' '
)
RETURNS @retArray TABLE
(
idx SMALLINT PRIMARY KEY,
value VARCHAR(8000)
)
AS BEGIN
DECLARE @idx SMALLINT,
@value VARCHAR(8000),
@bcontinue BIT,
@iStrike SMALLINT,
@iDelimlength TINYINT
IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END
SET @idx = 0
SET @sText = LTRIM(RTRIM(@sText))
-- Trim the last delimiter if it exists
IF ( RIGHT(@sText, LEN(@sDelim)) = @sDelim )
SELECT @sText = LEFT(@sText, ( LEN(@sText) - LEN(@sDelim) ))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1
IF NOT ( ( @iDelimlength = 0 )
OR ( @sDelim = 'Empty' )
)
BEGIN
WHILE @bcontinue = 1
BEGIN
--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
IF CHARINDEX(@sDelim, @sText) > 0
BEGIN
SET @value = SUBSTRING(@sText, 1,
CHARINDEX(@sDelim, @sText)
- 1)
BEGIN
INSERT @retArray ( idx, value )
VALUES ( @idx, @value )
END
--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTRIM(RIGHT(@sText,
DATALENGTH(@sText)
- @iStrike))
END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray ( idx, value )
VALUES ( @idx, @value )
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue = 1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText) > 1
BEGIN
SET @value = SUBSTRING(@sText, 1, 1)
BEGIN
INSERT @retArray ( idx, value )
VALUES ( @idx, @value )
END
SET @idx = @idx + 1
SET @sText = SUBSTRING(@sText, 2,
DATALENGTH(@sText) - 1)
END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray ( idx, value )
VALUES ( @idx, @sText )
SET @bcontinue = 0
END
END
END
RETURN
END
GO