SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
drop table if exists t1;

create table t1
(id     int
,x      varchar(30)
,y      int);

insert into t1 (id,x,y) values
(1,'a',1),
(2,'b',3),
(3,'c',2),
(4,'d',2),
(5,'e',1),
(6,'f',3);
6 rows affected

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from  (values (2),(1),(3) ) as dt(y)
)

select *
from   myorder
order by orderid;


orderid
y


1
2

2
1

3
3


with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from  (values (2),(3),(1) ) as dt(y)
)

select *
from   myorder
order by orderid;


orderid
y


1
2

2
3

3
1


with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from  (values (2),(3),(1) ) as dt(y)
)

select t1.id,
       t1.x,
       t1.y
       
from   t1
join   myorder mo
on     mo.y = t1.y

order by mo.orderid;


id
x
y


3
c
2

4
d
2

2
b
3

6
f
3

1
a
1

5
e
1


with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from  (values (2),(3),(1) ) as dt(y)
)

select t1.id,
       t1.x,
       t1.y
       
from   t1
join   myorder mo
on     mo.y = t1.y

order by mo.orderid, 
         t1.x desc


id
x
y


4
d
2

3
c
2

6
f
3

2
b
3

5
e
1

1
a
1


with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from  (values (2) ) as dt(y)
)

select t1.id,
       t1.x,
       t1.y
       
from   t1
left
join   myorder mo
on     mo.y = t1.y

order by isnull(mo.orderid, 100),
         t1.x desc


id
x
y


4
d
2

3
c
2

6
f
3

5
e
1

2
b
3

1
a
1