Return all for where IN (1,2,3,3,3,1) clause with

Question

I need to return all values for: select...where IN (1,2,3,3,3,1)

I have a table with unique IDs. I have the following query:

Select * From Table_1 Where ID IN (1,2,3,3,3,1);

So far I'm returning only 3 records for unique values (1,2,3)

I want to return 6 records.

I need to get result set shown on the picture.

Answer 1

You can do it. But not with IN.

Select 
src.* 
From Table_1 src
inner join (
 select
ID,
myorder
from (values
 (1,0),
(2,1),
(3,2),
(3,3),
(3,4),
(1,5)
) x (ID,myorder)
) T ON
T.ID = src.ID
order by T.myorder

Keep in mind if you want your dataset Ordered you have to supply the order by clause.

Answer 2

You can't do that with the IN operator. You can create a temporary table and JOIN:

CREATE TABLE #TempIDs
(
ID int
)

INSERT INTO #TempIDs (1)
INSERT INTO #TempIDs (2)
INSERT INTO #TempIDs (3)
INSERT INTO #TempIDs (3)
INSERT INTO #TempIDs (3)
INSERT INTO #TempIDs (1)

Select Table_1.* From Table_1
INNER JOIN #TempIDs t n Table_1.ID = t.ID;

Another (maybe uglier) option is to do a UNION:

Select * From Table_1 Where ID = 1
UNION ALL
Select * From Table_1 Where ID = 2
UNION ALL
Select * From Table_1 Where ID = 3
UNION ALL
Select * From Table_1 Where ID = 3
UNION ALL
Select * From Table_1 Where ID = 3
UNION ALL
Select * From Table_1 Where ID = 1

Answer 3

You cannot do this using the IN condition, because IN treats your items as a set (i.e. ensures uniqueness).

You can produce the desired result by joining to a UNION ALL, like this:

SELECT t.*
FROM Table_1 t
JOIN ( -- This is your "IN" list
          SELECT 1 AS ID
UNION ALL SELECT 2 AS ID
UNION ALL SELECT 3 AS ID
UNION ALL SELECT 3 AS ID
UNION ALL SELECT 3 AS ID
UNION ALL SELECT 1 AS ID
) x ON x.ID = t.ID