Order by with a particular value on TOP

Question

I have a requirement to order a list of countries alphabetically but with a specific country on TOP. After that country it should be ordered alphabetically . Example

India
Afghanistan
Albania
Algeria
American Samoa
Andorra
Angola
Anguilla
Antigua and Barbuda
Argentina
Armenia
Aruba 

........... I tried the answer provided here Sorting certain values to the top but it was not working I am using PL/SQl dev tool. Thanx in Advance

Answer 1

Something like this should work:

MySQL VERSION

ORDER BY (country = 'India') DESC, country ASC

SQLFIDDLE DEMO

-- or --

ORDER BY 
    CASE 
       WHEN country = 'India' THEN 1
       ELSE 2
    END,
    country ASC

SQLFIDDLE DEMO

ORACLE VERSION

ORDER BY 
    CASE 
       WHEN country = 'India' THEN 1 
       ELSE 2
    END

or you can have more than one specific value at top:

ORDER BY 
    CASE 
       WHEN country = 'India' THEN 1
       WHEN country = 'United Kingdom' THEN 2
       ELSE 3
    END

Answer 2

If there is no NULL values in the country column then you can use that one:

ORDER BY NULLIF(country, 'India') ASC NULLS FIRST