Ordering by Column Value in SQL Server & MySQL

Posted on September 27, 2009, 4:40 pm, by Rhys, under MySQL, T-SQL.

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

?View Code 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.

?View Code MYSQL
-- 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');

mysql ordering by column value thumb Ordering by Column Value in SQL Server & MySQL

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.

?View Code 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.

?View Code TSQL
-- 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;

sql server ordering by column value thumb Ordering by Column Value in SQL Server & MySQL

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

Tags: , , ,
Comment (RSS)  |  Trackback

Leave a Reply

«
»