SQL with Manoj

ORDER BY clause orders the result set of a SQL query for a particular or specific set of columns provided in ORDER BY clause.

Ever wonder to tweak that order. Lets take a scenario when you fill out an online form. When you reach at Country dropdown you get lots of County names to select. But if your Country is not listed there, there an “Other” option and that too at the bottom. Why it is not ordered with other Country names alphabetically? How come it reached that bottom? Lets see how can we get that resultset by using ORDER BY CASE.

-- Create a Country table
CREATE TABLE Country (ID INT IDENTITY(1,1), cname VARCHAR(50))

-- Insert dummy data
INSERT INTO Country
SELECT 'Afghanistan'
UNION
SELECT 'Australia'
UNION
SELECT 'France'
UNION
SELECT 'Oman'
UNION
SELECT 'United States'
UNION
SELECT 'Singapore'
UNION
SELECT 'Other'

-- SELECT with plain ORDER BY
SELECT * FROM Country
ORDER BY cname

ID	cname
1	Afghanistan
2	Australia
3	France
4	Oman
5	Other
6	Singapore
7	United States

Here you will see the "Other" option just below "Oman". It should not be here, 
because it is not a Country name and the user should should see this 
option at the bottom.

-- SELECT with ORDER BY CASE
SELECT * FROM Country
ORDER BY CASE WHEN cname='other' THEN 1 ELSE 0 END

ID	cname
1	Afghanistan
2	Australia
3	France
4	Oman
6	Singapore
7	United States
5	Other

Applying ORDER BY clause with CASE tweaks the "Other" option and places it at the 
bottom. Other than Country this logic can be applied for other things like, 
University Name, City, State, etc.

-- Final Cleanup
DROP TABLE Country