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

toookiAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Try:

select * from test order by to_number(regexp_substr(F1,'^[0-9]+'));
0
 
toookiAuthor Commented:
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.
0
 
sonicefuCommented:
select * from test order by substr(F1,1,4) desc;
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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

select * from test order by to_number(F1) DESC;
0
 
mayankagarwalCommented:
1001
1002
1001
103 ABC
103 ABC
102
101
100
11
10

this order??
0
 
slightwv (䄆 Netminder) Commented:
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]',''));
0
 
toookiAuthor Commented:
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.
0
 
sonicefuCommented:
select * from test order by to_number(regexp_replace(F1,'[^0-9]','')) desc;
0
 
mayankagarwalCommented:
split the field based on space, convert the string to number then sort it
0
 
sunny25Commented:
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;
0
 
awking00Commented:
You can also use regexp character class - see attached.
query.txt
0
 
toookiAuthor Commented:
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....
0
 
awking00Commented:
Just need to include the DESC keyword to get the sort order you indicated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.