Michael S Daniel Michael S Daniel - 7 months ago 13
SQL Question

How to perform Oracle sql order by with captial case string

I need to order my table rows as like below, They have a mix of full capital or First letter capital strings.

NewYork
Santa Clara
San (w) Jose
ATLANTA
LONDON


I tried using following query

select city_name from city
order by
case
when city_name=upper(city_name) then 2
else 1 end


I get ordered rows, but in random like the one below,

San (w) Jose
NewYork
Santa Clara
London
Atlanta


Any help to sort the rows with Capital string first is much appreciated.
Thanks

Answer

I am guessing that you want to order by city after you order by the case. You can add this to the order by:

select city_name
from city
order by (case when city_name=upper(city_name) then 2 else 1 end),
         city_name;

Oracle is case-sensitive by default so this should work.