• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

Oracle Order by clause

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
toooki
Asked:
toooki
  • 3
  • 2
  • 2
  • +4
1 Solution
 
toookiAuthor Commented:
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
 
sonicefuCommented:
select * from test order by substr(F1,1,4) desc;
0
 
AriMcCommented:
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.

 
mayankagarwalCommented:
1001
1002
1001
103 ABC
103 ABC
102
101
100
11
10

this order??
0
 
slightwv (䄆 Netminder) Commented:
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
 
toookiAuthor Commented:
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
 
sonicefuCommented:
select * from test order by to_number(regexp_replace(F1,'[^0-9]','')) desc;
0
 
slightwv (䄆 Netminder) Commented:
Try:

select * from test order by to_number(regexp_substr(F1,'^[0-9]+'));
0
 
mayankagarwalCommented:
split the field based on space, convert the string to number then sort it
0
 
sunny25Commented:
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
 
awking00Commented:
You can also use regexp character class - see attached.
query.txt
0
 
toookiAuthor Commented:
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
 
awking00Commented:
Just need to include the DESC keyword to get the sort order you indicated.
0

Featured Post

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!

  • 3
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now