asked on

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_~

Nitin Sontakke

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.

Nitin Sontakke

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

Nitin Sontakke

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial