?
Solved

Oracle Order by clause

Posted on 2011-03-10
13
Medium Priority
?
516 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 1900 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

770 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