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
– Panagiotis Kanavos, Commented Mar 15, 2019 at 8:56
Why do you want to return results in that order? Is there some logic behind it? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 15, 2019 at 8:58

Tim Biegeleisen · Accepted Answer · 2019-03-15 08:53:55Z

2

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;

answered Mar 15, 2019 at 8:53

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL search query with multiple values using in statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related