Solved

Oracle Order by clause

Posted on 2011-03-10
13
509 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
  • 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
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.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 58
Teradata converting character to integer 2 26
pivot rows to columns 1 35
date show only hh:mm 2 27
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

829 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