Move one exact row to top of all rows leaving all other ASCENDING

Question

Hello I wonder how can it be done in SQL server.

My code doesn't work.

SELECT * FROM
TABLE_NAME
WHERE NAME = 'United Kingdom'
ORDER BY Name

Here is the sample:

And here is expected result:

Thank you for your help.

Answer 1

Use a case in the order by:

SELECT *
FROM TABLE_NAME
ORDER BY (CASE WHEN NAME = 'United Kingdom' THEN 1 ELSE 2 END), Name;

ORDER BY accepts multiple keys. The first puts the desired values first.