SQL Server Order SELECT List with condition
For a simple solution based on just the one 'y=2' value (as per comments), the suggested order by case ... construct should suffice.

However, if the requirement is to be able to sort by a varying number of 'y' values, or perhaps order by a combination of columns ...



One idea that comes to mind ... use a table value constructor (TVC) to build an 'inline' table of your desired sort order; we'll then join this TVC back to your original data set to provide the desired ordering sequence.

We'll start by creating our table and populating with some sample data:

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);


For our TVC we'll generate a single-column derived table called dt(y) that includes the y values listed in their desired sort order.  We'll add the row_number() function to provide the column that we'll actually order by.  Finally, we'll wrap all of this in a common table expression (CTE) for ease of reference.

-- order by 'y' values 2, 1, 3:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(1),(3) ) as dt(y)  -- desired sort order is 2, 1, 3
)
select *
from   myorder
order by orderid;

 orderid |  y
 ------- | --
 1       |  2
 2       |  1
 3       |  3

-- order by 'y' values 2, 3, 1:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(3),(1) ) as dt(y)    -- desired sort order is 2, 3, 1
)
select *
from   myorder
order by orderid;

 orderid |  y
 ------- | --
 1       |  2
 2       |  3
 3       |  1


We can now join our TVC with the original data set (table t1 in this case) to generate the desired sort order:

-- sort solely on the TVC's 'y' column:

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

-- sort by TVC's 'y' column (ascending order), then by t1's 'x' column (descending order):

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


The key item to remember is to make sure the TVC's records are listed in an order that matches the desired sort order.



One potential issue is that if you don't know how many 'y' values you have, or perhaps you only need to sort by a handful of known 'y' values (and don't care about ordering of other 'y' values), you could:


limit the TVC to just those 'y' values you're interested in and ...
join the main data set via an outer join to the TVC and ...
then default the the missing TVC's 'orderid' columns to some sufficiently large number to ensure their location in the sort order does not affect the 'y' values of interest


For example, assume we want 'y=2' values to be listed first, and we don't care about the rest of the 'y' values:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2) ) as dt(y)        -- only interested in 'y=2' records
)

select t1.id,
       t1.x,
       t1.y

from   t1                            -- left join w/ TVC/CTE
left
join   myorder mo
on     mo.y = t1.y

order by isnull(mo.orderid, 100),    -- default missing TVC.orderid's to 100
         t1.x desc

 id | x  |  y
 -- | -- | --
  4 | d  |  2   -- 'y=2' records show up
  3 | c  |  2   -- first in our output

  6 | f  |  3   -- while we let the database engine
  5 | e  |  1   -- determine the order of the rest
  2 | b  |  3   -- of the result set; granted, for this
  1 | a  |  1   -- example we also had 't1.x desc'


Obviously you could still add other columns to the 'order by' clause to affect the final ordering.

Here's a dbfiddle for all of the above.



While the example queries (above) are based on hard coding the sort order for the 'y' column, this method could be expanded to allow for hard coding the sort order for any combination of columns.

Let's say for example you want a hard coded sort order based on 'y=2' and 'x in ('a','b','c')', the CTE/TVC would look like:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        x
        y
 from   (values (2,'a'),(2,'b'),(2,'c') ) as dt(x,y)
)


The main query could then be (outer) joined to myorder on the x and y columns:

with myorder as(...)

select ...

from   t1
left
join   myorder mo
on     mo.x = t1.x
and    mo.y = t1.y

order by isnull(mo.orderid, 100);


Again, if you don't need the flexibility then the more simple order by case ... construct should be sufficient.