CODEWITHSUNDEEP
sqloracle-databasesortingplsql
Jamil Smith
Jamil Smith

Reputation: 77

oracle PL/SQL: sort rows

I run a query with a order by but my column is not the order a want to be in

I wanted it like this:

PRODUCT

PROD_1
PROD_2
PROD_3
PROD_4
PROD_5
PROD_6
PROD_7
PROD_8
PROD_9
PROD_10

but it gives me this

PRODUCT

PROD_1
PROD_10
PROD_2
PROD_3
PROD_4
PROD_5
PROD_6
PROD_7
PROD_8
PROD_9

Upvotes: 1

Views: 747

Answers (4)

Harrison
Harrison

Reputation: 9090

You will need to sort twice (note I changed from regexp_replace to regexp_substr to allow for null return value)

with
    a as (
    select 'PROD_1' product from dual union all 
    select 'PROD_10' product from dual union all 
    select 'PROD_2' product from dual union all 
    select 'PROD_3' product from dual union all 
    select 'PROD_4' product from dual union all 
    select 'PROD_5' product from dual union all 
    select 'PROD_6' product from dual union all 
    select 'PROD_7' product from dual union all 
    select 'PROD_8' product from dual union all 
    select 'PROD_9' product from dual union all 
    select 'DECEAD_1' product from dual union all 
    select 'DECEAD_10' product from dual union all 
    select 'DECEAD_2' product from dual union all 
    select 'DECEAD_20' product from dual union all 
    select 'TREE_FROG' product from dual 
    )
    select PRODUCT
          , regexp_substr(product,'[^[:digit:]]*')  --return all non numeric
          , regexp_substr(product,'[0-9]+') 
          from a 
    order by regexp_substr(product,'[^[:digit:]]*')  ,
             TO_NUMBER(regexp_substr(product,'[0-9]+')) --note explicit numeric cast
    ;

Upvotes: 0

ejb_guy
ejb_guy

Reputation: 1125

    SELECT to_number(substr(colname,INSTR(column_name,'_')+1))) prodno, column_name 
    from table_name
    order by prodno

Not very elegant solution, but this should work. (As I don;t have access to Oracle, parameters to function might need tweaking.) This first get the position on _ using which it gets the number using sub-string which is then converted to number. You might also have to look at performance if table size is big

Upvotes: 0

Jeff Watkins
Jeff Watkins

Reputation: 6359

You're trying to lexographically sort something which is actually partially numeric. You could prefix zeroes (i.e. PROD_000001) but that's brittle. In real application, I assume that Prod 10 is actually temporally later than Prod 1, so you'd order by creation datetime.

Upvotes: 0

xQbert
xQbert

Reputation: 35323

You'll either need to add a 0 for each position such as 01 for numbers 1-99 or 001 for 1-999. OR you'll have to split out the numeric values and sort on two different columns.

Ask Tom

Upvotes: 1

Related Questions