asked on

Oracle Order by clause

I have Oracle 11g database.

Here is query:
select * from test order by F1 DESC;

But I do nit get the correct order:

11
103 ABC
103 ABC
102
101
1002
1001
100
10

I need :
1001
1002
1001
103 ABC
103 ABC
102
101
100
11
10

How do I get that

create table test (F1 varchar2(100));

insert into test values ('1001');
insert into test values ('1002');
insert into test values ('100');
insert into test values ('101');
insert into test values ('10');
insert into test values ('11');
insert into test values ('102');
insert into test values ('103 ABC'); 
commit;
select * from test order by F1;

Open in new window

toooki

ASKER

Also if the value of F1 is:
11THis is
12-That is

I need to use those as 11 and 12 when sorting ignoring the rest of the non-numeric characters.

sonicefu

select * from test order by substr(F1,1,4) desc;

AriMc

I don't have Oracle available at the moment but you could try:

select * from test order by to_number(F1) DESC;

mayankagarwal

1001
1002
1001
103 ABC
103 ABC
102
101
100
11
10

this order??

slightwv (䄆 Netminder)

What eveyone is saying: You are performing an ASCII sort not a NUMERIC sort.

Do you want to sort just on the numbers?

maybe something like:

select * from test order by to_number(regexp_replace(F1,'[^0-9]',''));

toooki

ASKER

Sorry, this order:
1002
1001
1001
103 ABC
103 ABC
102
101
100
11
10

I need to ignore everything after the first non-numeric character when deciding the sorting order.

select * from test order by to_number(F1) DESC;
Gives error: Invalid Number

select * from test order by substr(F1,1,4) desc;
Also the length of F1 is not fixed and its numeric part length is not foxed either. The numeric part lenght could be 5-6 digits too.

sonicefu

select * from test order by to_number(regexp_replace(F1,'[^0-9]','')) desc;

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mayankagarwal

split the field based on space, convert the string to number then sort it

sunny25

Try this one; It should give you the desired result
select * from test order by to_number(substr(F1,1,decode(instr(F1,' '),0,length(F1),instr(F1,' ')))) desc;

awking00

You can also use regexp character class - see attached.
query.txt

toooki

ASKER

Many thanks for all the help...
I tried all that you suggest..
select * from test order by to_number(regexp_substr(F1,'^[0-9]+'));
This worked for me in the perfect way....

awking00

Just need to include the DESC keyword to get the sort order you indicated.