Search code examples
sqlplsql

Order by on length of the string and then by single alphabet desc in pl sql

I have a set of single letters and double letters that needs to be ordered.

Data : 
A
B
D
E
F
G
AL
BL
DL

Output :
AL
BL
DL
E
F
G
A
B
D

I can achieve the following result by query 

SELECT column_name
        FROM table_name
   ORDER BY LENGTH(column_name) desc, column_name asc;

AL
BL
DL
A
B
D
E
F
G

but how to place A B D after E F G

Any help will be appriciated...

Solution

  • One way to do this is to use additional information on the maximum length based on the first character in the string. So, 'A' would have a value of 2 and 'G' would have a value of 1:

    SELECT column_name
FROM (select t.*,
             max(length(column_name)) over (partition by substr(column_name, 1, 1)) as fclen
      from table_name t
     ) t
ORDER BY LENGTH(column_name) desc,
         (case when fclen = 1 then 1 else 0 end) desc,
         column_name asc;