Oracle custom sort

Question

The query...

select distinct name from myTable 

returns a bunch of values that start with the following character sequences...

ADL*
FG*
FH*
LAS*
TWUP*

Where '*' is the remainder of the string.

I want to do an order by that sorts in the following manner...

ADL*
LAS*
TWUP*
FG*
FH*

But then I also want to sort within each name in the standard order by fashion. So, an example, if I have the following values

LAS-21A
TWUP-1
FG999
FH3
ADL99999
ADL88888
ADL77777
LAS2

I want it to be sorted like this...

ADL77777
ADL88888
ADL99999
LAS2
TWUP-1
FG999
FH3

I initially thought I could accomplish this vias doing an order by decode(blah) with some like trickery inside of the decode but I've been unable to accomplish it. Any insights?

Answer 1

Goofy and verbose, but should work:

select name, case when substr (name, 1, 3) = 'ADL'  then 1  
                  when substr (name, 1, 3) = 'LAS'  then 2
                  when substr (name, 1, 4) = 'TWUP' then 3
                  when substr (name, 1, 2) = 'FG'   then 4
                  when substr (name, 1, 2) = 'FH'   then 5
                  else 6
             end SortOrder
from myTable
order by 2, 1;

Not sure if 6 is the correct place to sort the other items, but it is obvious how to fix that. At least it is clear what is going on, even if I have no idea why you are doing it this way.

EDIT: If these are the only values, you could change lines 4 and 5:

select name, case when substr (name, 1, 3) = 'ADL'  then 1  
                  when substr (name, 1, 3) = 'LAS'  then 2
                  when substr (name, 1, 4) = 'TWUP' then 3
                  when substr (name, 1, 1) = 'F'    then 4
                  else 6
             end SortOrder
from myTable
order by 2, 1;

ANOTHER EDIT: And again, if these are the only values, you can simplify even more. Since the only one out of order is the F* series, you can force them to the end, and use the actual first letter for all the others. This is simpler, but relies too much on the exact values for my preference. On the other hand, it does remove many of the seemingly unnecessary calls to substr :

select name, case when substr (name, 1, 1) = 'F'  then 'Z'
                  else name
             end SortOrder
from myTable
order by 2, 1;

Answer 2

The problem is that your prefix contains a variable number of characters. This is a good time to deploy regular expressions (if you have 10g or higher).

SQL> select cola
  2  from t34
  3  order by decode( regexp_substr(cola, '[[:alpha:]]+')
  4                   , 'ADL' , 10
  5                    , 'LAS',  20
  6                    , 'TWUP',  30
  7                    , 'FG' , 40
  8                    , 'FH' , 50
  9                    , 60 )
 10           , cola
 11  /

COLA
----------
ADL77777
ADL88888
ADL99999
LAS-21A
LAS2
TWUP-1
FG999
FH3

8 rows selected.

SQL>

If earlier versions of Oracle we can use the OWA_PATTERN.AMATCH() function to the same effect:

SQL> select cola
  2  from t34
  3  order by decode( owa_pattern.amatch(cola, 1, '^[A-Z]+')
  4                   , 'ADL' , 10
  5                    , 'LAS',  20
  6                    , 'TWUP',  30
  7                    , 'FG' , 40
  8                    , 'FH' , 50
  9                    , 60 )
 10           , cola
 11  /

COLA
----------
ADL77777
ADL88888
ADL99999
FG999
FH3
LAS-21A
LAS2
TWUP-1

8 rows selected.

SQL>