ORACLE:- 'SELECT ORDER BY ASC' but 'USA' always first.

Question

I have to write a drop down query for countries. But USA should always be first. The rest of the countries are in alphabetical order I tried the following query

SELECT      
           countries_id
        ,countries_name

        FROM get_countries
        WHERE 
             countries_id = 138
        UNION 

        SELECT 
                countries_id
              ,countries_name

         FROM get_countries 
        WHERE   
                  countries_id != 138
        ORDER BY 2 ASC

Answer 1

Something like this maybe:

ORDER BY
  CASE 
     WHEN upper(country_name) = 'USA' then '0'
     ELSE lower(country_name)
  END

Here's a complete example

create TABLE countries (country_name VARCHAR2(50));

INSERT INTO countries VALUES ('USA');
INSERT INTO countries VALUES ('India');
INSERT INTO countries VALUES ('Russia');
INSERT INTO countries VALUES ('China');
COMMIT;

SELECT country_name
  FROM countries
ORDER BY
  CASE 
     WHEN upper(country_name) = 'USA' then '0'
     ELSE lower(country_name)
  END

Returns:

USA
China
India
Russia

Answer 2

It's been a while since I worked with oracle, but you can try ORDER BY countries_name = 'USA', countries_name ASC.

Correction

Sorry that didn't work. I had "countries_name" mis-typed as "country_name", so it may work now.

You could also use ORDER BY decode(countries_name, 'USA', 0, 1), countries_name ASC.