SQL search query with multiple values using in statement

Question

i am very new to sql databases and i have the following question

I need to search in the component table for all components that have the ID's matching a list of ID's (see example).

Component table

ID      Name   
1       component 1
...     ...
10      component 10

List of ID's = (1,8,3)

query for this: 
SELECT * FROM Component
WHERE ID IN (1,8,3)

This will give me ID 1,3 and 8, that is correct, but in a different order. Is there a way to keep the order like the order of the list (1,8,3 instead of 1,3,8)?

the link that provided the query: SQL Statement using Where clause with multiple values

Thanks in advance, Theo

SQL results have no implicit order. That holds for *all* databases, not just SQL Server. Unless you use an `ORDER BY` clause, the server will return results as they are produced. — Panagiotis Kanavos, Mar 15 '19 at 08:56
Why do you want to return results in *that* order? Is there some logic behind it? — Panagiotis Kanavos, Mar 15 '19 at 08:58

score 2 · Accepted Answer · answered Mar 15 '19 at 08:53

You may order using a CASE expression:

SELECT *
FROM Component
WHERE ID IN (1,8,3)
ORDER BY
    CASE WHEN ID = 1 THEN 1
         WHEN ID = 8 THEN 2
         ELSE 3 END;

But a much better long term solution might be to maintain a separate table of target ID values and their ordering:

WITH cte AS (
    SELECT 1 AS ID, 1 AS position UNION ALL
    SELECT 8, 2 UNION ALL
    SELECT 3, 3
)

SELECT c.*
FROM Component c
INNER JOIN cte t
    ON c.ID = t.ID
WHERE c.ID IN (1,8,3)
ORDER BY t.position;

SQL search query with multiple values using in statement

1 Answers1