Solved

Oracle Order by clause

Posted on 2011-03-10
13
513 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:toooki
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
13 Comments
 

Author Comment

by:toooki
ID: 35098339
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
 
LVL 13

Expert Comment

by:sonicefu
ID: 35098357
select * from test order by substr(F1,1,4) desc;
0
 
LVL 9

Expert Comment

by:AriMc
ID: 35098433
I don't have Oracle available at the moment but you could try:

select * from test order by to_number(F1) DESC;
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35098437
1001
1002
1001
103 ABC
103 ABC
102
101
100
11
10

this order??
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35098475
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
 

Author Comment

by:toooki
ID: 35098490
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
 
LVL 13

Expert Comment

by:sonicefu
ID: 35098512
select * from test order by to_number(regexp_replace(F1,'[^0-9]','')) desc;
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 475 total points
ID: 35098526
Try:

select * from test order by to_number(regexp_substr(F1,'^[0-9]+'));
0
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35104477
split the field based on space, convert the string to number then sort it
0
 
LVL 1

Expert Comment

by:sunny25
ID: 35106146
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
 
LVL 32

Expert Comment

by:awking00
ID: 35116771
You can also use regexp character class - see attached.
query.txt
0
 

Author Comment

by:toooki
ID: 35167391
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
 
LVL 32

Expert Comment

by:awking00
ID: 35171995
Just need to include the DESC keyword to get the sort order you indicated.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question