SQL: Order By The Sequence in the "IN" Statement

Question

The Base Data:

Note this is all SQL Server.

Okay so the base table will look something like this(call it NameTable):

ID    |    Name
___________________
1     |    Randy
2     |    Kenny
3     |    Stan
4     |    Kyle
5     |    Cartman

Now I will run a SQL query:

SELECT * FROM [NameTable] WHERE Name IN ("Kyle", "Stan", "Kenny", "Cartman", "Randy")

The Current Result:

ID    |    Name
___________________
1     |    Randy
2     |    Kenny
3     |    Stan
4     |    Kyle
5     |    Cartman

This is as expected. And that is fine, but what I need is to order the data by the order they appear in the IN statement.

So in this case: Kyle, Stan, Kenny, Cartman, Randy.

Take special note that this is NOT going to work with standard order by, since it won't necessarily be in alphabetic order

The Needed Result:

 ID    |    Name
___________________
4     |    Kyle
3     |    Stan
2     |    Kenny
5     |    Cartman
1     |    Randy

Question

Basically the order of the items in the IN clause is the order I need the names by.

How should I adjust the select query to achieve this result? Is it even possible?

PS: I did not think a sqlfiddle necessary since the data is pretty straight forward, but I will add one if the question is still unclear?

Also I saw some other posts on this, but they weren't for SQL Server.

You could add an orderid. – Jochen Schultz Jul 03 '15 at 08:36 — Jochen Schultz, Jul 03 '15 at 08:36

score 7 · Accepted Answer · answered Jul 03 '15 at 08:37

You can do something like:

SELECT *
FROM   dbo.NameTable
ORDER BY CASE WHEN Name = 'Kyle' THEN 1
          WHEN Name = 'Stan' THEN 2
          WHEN Name = 'Kenny' THEN 3
          WHEN Name = 'Cartman' THEN 4
          WHEN Name = 'Randy' THEN 5
          ELSE 6 END ASC

SQL: Order By The Sequence in the "IN" Statement

The Base Data:

The Current Result:

The Needed Result:

Question

1 Answers1