CODEWITHSUNDEEP
sqloracle-databasesortingsql-order-by
Michael S Daniel
Michael S Daniel

Reputation: 59

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

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

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.

Upvotes: 2

Related Questions