[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Oracle Order by clause

Posted on 2011-03-10
13
Medium Priority
?
517 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
What we learned in Webroot's webinar on multi-vector protection.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

650 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