Oracle query to sort numeric first

I have an Oracle Table Docs with  DocNoWithRev column  ( among many other columns in the table)
I need to do a sort where revNo numerics come first  in descending order and then the alpha numeric  while sorting by DocNoWithRev  column

Sample data
   DocNoWithRev
     Doc1_1
     Doc1_~
     Doc1_2
      Doc1_0
      Document2_1
      Document2_~
     Document2_0
     Doc1_3
      .......    more rows
     
 I need it sorted as
  Doc1_3
 Doc1_2
 Doc1_1
 Doc1_0
 Doc1_~
 Document2_1
 Document2_0
 Document2_~
Sam OZAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Why post SQL Server code in an Oracle question?  I'm also not sure how SQL Server sorts but in the first example the '~' should be first in a descending ascii sort before 0-9.


I'm assuming the document numbers may not always be exactly 4 characters long and you might have some like 'Doc11_2' or 'Doc123_4'.

Try this:
drop table docs purge;
create table docs(DocNoWithRev varchar2(50));

insert into docs values('Doc1_1');
insert into docs values('Doc1_~');
insert into docs values('Doc1_2');
insert into docs values('Doc1_0');
insert into docs values('Document2_1');
insert into docs values('Document2_~');
insert into docs values('Document2_0');
insert into docs values('Doc1_3');
commit;

select DocNoWithRev, regexp_substr(DocNoWithRev,'[0-9].+$') rev from docs order by regexp_replace(regexp_substr(DocNoWithRev,'[0-9].+$'),'[^0-9]',' ') desc;

Open in new window

1
 
Nitin SontakkeDeveloperCommented:
Can you the paste the output which you are getting when you issue the ORDER BY on that column, please? The sample data given by you above doesn't look the which is ordered by already.

Much helpful if you give existing output of ORDER BY clause.
0
 
Nitin SontakkeDeveloperCommented:
I don't have Oracle installed, but in SQL Server this is what I get:

declare @docs table
(
  [DocNoWithRev] varchar(20) not null
)

insert into @docs ([DocNoWithRev]) values 
 ('Doc1_1')
,('Doc1_~')
,('Doc1_2')
,('Doc1_0')
,('Document2_1')
,('Document2_~')
,('Document2_0')
,('Doc1_3')

select *
from @docs 
order by 1 desc

-- Output

DocNoWithRev
--------------------
Document2_1
Document2_0
Document2_~
Doc1_3
Doc1_2
Doc1_1
Doc1_0
Doc1_~

Open in new window

0
 
Nitin SontakkeDeveloperCommented:
Here is a SQL Server query...essentially you will need to split the contents and then sort. I have no idea of the entire set of data values. You will need to work on this one...

Using same table as above...

select *, substring(replace([DocNoWithRev], 'ument', ''), 4, 1) part1, substring(replace([DocNoWithRev], 'ument', ''), 6, 1) part2
from @docs 
order by 2, 3 desc

-- Output

DocNoWithRev         part1 part2
-------------------- ----- -----
Doc1_3               1     3
Doc1_2               1     2
Doc1_1               1     1
Doc1_0               1     0
Doc1_~               1     ~
Document2_1          2     1
Document2_0          2     0
Document2_~          2     ~

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.