Any standard SQL expression to sort results base on IN expression [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

SQL - order by list order

Is there any standard SQL expression to sort results exactly base on IN expression. For example to return the results of the following query so that 2, 4, 6, 8 are returned serially?

SELECT * FROM SOMETABLE WHERE ID IN (2, 4, 6, 8开发者_StackOverflow社区)

---

The closest thing I can think of is doing a JOIN instead of an IN to a table with the original order with their ordinal rank

SELECT * FROM SOMETABLE INNER JOIN SOMETABLE2 ...  etc
ORDER BY SOMETABLE2.original

---

If you have full controlled over your SQL rendering, then use a CASE expression:

ORDER BY CASE ID 
-- render WHEN clauses in the desired order
WHEN 2 THEN 1 
WHEN 4 THEN 2
WHEN 6 THEN 3
WHEN 8 THEN 4
END

---

Assuming you can pass the ids as a fixed delimited string, you can do the following :

-- Populate a number table
DECLARE @Temp Table(Number int)

INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(2)
INSERT INTO @Temp VALUES(3)
INSERT INTO @Temp VALUES(4)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(7)
INSERT INTO @Temp VALUES(8)
INSERT INTO @Temp VALUES(9)
INSERT INTO @Temp VALUES(10)

SELECT TOP 8000 Number = IDENTITY(int,1,1) INTO [dbo].Numberos FROM @TEMP t1, @TEMP t2, @TEMP t3, @TEMP t4

ALTER TABLE [dbo].[Numbers] ADD CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED  ([Number])

-- This function splits a fixed delimited string into a table object
CREATE FUNCTION [dbo].[fixstring_single](@str text, @itemlen tinyint) 
RETURNS TABLE 
AS 
RETURN (SELECT listpos = n.Number, 
str = substring(@str, (@itemlen + 1) * (n.Number - 1) + 1, @itemlen) 
FROM Numbers n 
WHERE n.Number <= (datalength(@str)+1) / (@itemlen+1)) 

DECLARE @ids varchar(100);
SET @ids = '00002 00001 00004';

-- Join to the number table ordered by the posisiton in the ids string
SELECT * FROM TestT INNER JOIN fixstring_single(@ids, 5) S ON TestT.ID = S.Str ORDER BY S.listpos