Ordering by Column Value in SQL Server & MySQL

Today I needed to order some data by specific column value and I recalled the really handy FIELD function in MySQL. Here’s a demo of this feature in MySQL

# Test table
CREATE TABLE City
(
	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	City VARCHAR(50)
);

# Insert some test data
INSERT INTO City
(
	City
)
VALUES
(
	'London'
),
(
	'Hong Kong'
),
(
	'New York'
),
(
	'Bangkok'
),
(
	'Los Angeles'
),
(
	'Paris'
),
(
	'Singapore'
),
(
	'Moscow'
),
(
	'Lagos'
),
(
	'Johannesburg'
);

Using the FIELD function we can order the resultset how ever we like.

-- Ordering by the value of City
SELECT *
FROM City
ORDER BY FIELD(City, 'Moscow',
		     'Lagos',
		     'Hong Kong',
		     'New York',
		     'Johannesburg',
		     'Singapore',
		     'London',
		     'Paris',
		     'Bangkok',
		     'Los Angeles');

This is all very well but I needed to do this using SQL Server. I immediately though of using a CASE statement to do the ordering but thought I’d try to find something as easy as the FIELD function. This search proved to be unfruitful with the documentation pointing me nowhere. Should anybody know of a simpler way then please let me know. In the end I settled for a ordering using a CASE statement. The script below contains 2008 TSQL.

-- Test table
CREATE TABLE City
(
	Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
	City VARCHAR(50)
);

-- Insert some test data
INSERT INTO City
(
	City
)
VALUES
(
	'London'
),
(
	'Hong Kong'
),
(
	'New York'
),
(
	'Bangkok'
),
(
	'Los Angeles'
),
(
	'Paris'
),
(
	'Singapore'
),
(
	'Moscow'
),
(
	'Lagos'
),
(
	'Johannesburg'
);

Now lets order our data by the value of the City column.

-- Ordering by the value of City
SELECT *
FROM City
ORDER BY CASE(City)
		WHEN 'Moscow' THEN 1
		WHEN 'Lagos' THEN 2
		WHEN 'Hong Kong' THEN 3
		WHEN 'New York' THEN 4
		WHEN 'Johannesburg' THEN 5
		WHEN 'Singapore' THEN 6
		WHEN 'London' THEN 7
		WHEN 'Paris' THEN 8
		WHEN 'Bangkok' THEN 9
		WHEN 'Los Angeles' THEN 10
	 END;

Both methods are perfectly functional but MySQL wins for me, on the grounds on simplicity, and a few less keystrokes!