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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
slightwv (䄆 Netminder) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.