Order by case insensitive in oracle

I want to order a following text in following order but, after trying the following query it is not working.

values to order is “A”, “B”, “Y”, “Z”, “a”, “b”, “y”, “z”.

Expected result “ZzYyBbAa”

SELECT COL FROM TABLE ORDER BY COL DESC;
SELECT COL FROM TABLE ORDER BY UPPER/LOWER(COL) DESC; Result-> ZzYybBaA
SELECT COL FROM TABLE ORDER BY NLS_UPPER/NLS_LOWER(COL) DESC; Result-> ZzYybBaA

Answer

First of all, you can order by the UPPER (or LOWER) case of the column, but once you’ve done that, you then need to sort by the text itself to get the order on the initial letter; eg:

with sample_data as (select 'A' txt from dual union all
                     select 'B' txt from dual union all
                     select 'Y' txt from dual union all
                     select 'Z' txt from dual union all
                     select 'a' txt from dual union all
                     select 'b' txt from dual union all
                     select 'y' txt from dual union all
                     select 'z' txt from dual)
select txt
from   sample_data
order by upper(txt) desc, txt;


TXT
---
Z  
z  
Y  
y  
B  
b  
A  
a