Solved

Oracle Order by clause

Posted on 2011-03-10
13
511 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
Industry Leaders: 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!

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2008 R2 2 31
oracle query 3 27
Populate a MS Access field based on contents of 2 other fields. 5 29
Oracle perfomance issue. 4 24
Read about achieving the basic levels of HRIS security in the workplace.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

730 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