SQL Server - Order by case

Question

I am trying to get the following query to display the results in alphabetical order by City except for the fact that I want "Berlin" to appear at the top of the list

So the results would look something like

• Berlin
• Algeria
• Australia
• Fiji
• Greece
• ...

Hope that makes sense,

I currently have the following...

SELECT CompanyName, City
FROM customers
ORDER BY case when City = 'Berlin' END

Answer 1

SELECT CompanyName, City 
FROM customers 
ORDER BY case when City = 'Berlin' then 1 else 999 END asc

Answer 2

Almost:

SELECT CompanyName, City
FROM customers
ORDER BY CASE WHEN City = 'Berlin' THEN 0 ELSE 1 END, City

Answer 3

Try something like

SELECT CompanyName, City 
FROM customers 
ORDER BY case when City = 'Berlin' THEN 0 ELSE 1 END, City

Answer 4

SELECT CompanyName, City, CASE WHEN City = 'Berlin' THEN 0 ELSE 1 END AS Ordering
FROM customers
ORDER BY Ordering, City

Answer 5

As blank string appears first in any string ordered list, all other results sorted normally. So this works perfectly:

SELECT CompanyName, City
FROM customers
ORDER BY CASE WHEN City = 'Berlin' THEN '' ELSE City END

Tested with:

CREATE TABLE customers (CompanyName VARCHAR(50), City VARCHAR(50))

INSERT INTO customers VALUES ('Customer1', 'Berlin')
INSERT INTO customers VALUES ('Customer2', 'Algeria')
INSERT INTO customers VALUES ('Customer3', 'Australia')
INSERT INTO customers VALUES ('Customer4', 'Fiji')
INSERT INTO customers VALUES ('Customer5', 'Greece')

SELECT CompanyName, City
FROM customers
ORDER BY CASE WHEN City = 'Berlin' THEN '' ELSE City END

-- OUPUT
-- Customer1    Berlin
-- Customer2    Algeria
-- Customer3    Australia
-- Customer4    Fiji
-- Customer5    Greece

Answer 6

How about using Union? Something like this, for example:

SELECT 1 as Weight, CompanyName, City FROM customers
WHERE city='Berlin'
UNION ALL
SELECT 2 as Weight, CompanyName, City FROM
customers 
WHERE city<>'Berlin'
ORDER BY Weight, City