Oracle SQL to Sort Version Numbers

Question

In Oracle, just using the ORDER BY does not sort version numbers. My Version_Number field is declared as a VARCHAR and I cannot change it. For Example: The following versions :

should be sorted as

I have researched several posts but none of them seem to really serve my purpose or the answers were intended for SQL Server, etc and not Oracle. I came across this particular sql which seemed to look like it worked.

select version_number from mytable 
order by lpad(version_number, 4) desc;

which sorted the versions in this fashion:

I believe this sql statement works for SQL Server :

select version_number from mytable 
order by cast ('/' + replace(version_number , '.', '/') + '/' as hierarchyid) desc;

However, this does not work with Oracle. Is there an alternative to hierarchyid in Oracle?

Is there anyone who can come up with a SQL to sort these versions accurately?

I have already seen the posts mentioned below (links attached). So kindly do not tell me that this post is a duplicate.
SQL sort by version "number", a string of varying length
How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query
Version number sorting in Sql Server
mysql sorting of version numbers
and many more.

Vamsi Prabhala · Accepted Answer · 2015-11-30T14:10:46.477

4

This is one way to do it. First order by the number before . and then by the numbers after .

select version_number 
from mytable 
order by substr(version_number, 1, instr(version_number,'.')-1) desc
        ,length(substr(version_number, instr(version_number,'.')+1)) desc
        ,substr(version_number, instr(version_number,'.')+1) desc

edited Nov 30 '15 at 14:10

answered Nov 30 '15 at 14:02

Vamsi Prabhala

48,685
4
36
58

Thanks for your answer. But it is sorting it in this way: 2.0, 1.9, 1.8, 1.7, 1.6, 1.5, 1.4, 1.3, 1.20, 1.2, 1.19, 1.18, 1.17, 1.16, 1.15, 1.14, 1.13, 1.12, 1.11, 1.10, 1.1, 1.0 – Arthas Nov 30 '15 at 14:08
Do you think you could tweak it a little bit to handle the sorting in the case when there is no decimal point in the version? For example: if there is a "1" , it displays this way : 1, 1.20, 1.18, 1.17, 1.16, 1.10, 1.9, 1.2, 1.1, 1.0 – Arthas Nov 30 '15 at 14:49

score 3 · Answer 2 · answered Aug 22 '17 at 03:27

This SQL supports your input data plus any included Revision or Build digits.

with 
   inputs as (select '1.20'         as version_number from dual union all  
              select '1.9'          as version_number from dual union all
              select '1.18'         as version_number from dual union all
              select '1.13'         as version_number from dual union all
              select '1.5'          as version_number from dual union all
              select '1.11'         as version_number from dual union all
              select '2.0'          as version_number from dual union all
              select '1.8'          as version_number from dual union all
              select '1.3'          as version_number from dual union all
              select '1.2'          as version_number from dual union all
              select '1.1'          as version_number from dual union all
              select '1.0'          as version_number from dual union all
              select '1.10'         as version_number from dual union all
              select ' 3.1 '        as version_number from dual union all
              select '3.1.1000'     as version_number from dual union all
              select '3.1.1'        as version_number from dual union all
              select '3.1.100'      as version_number from dual union all
              select '3.1.2.1000'   as version_number from dual union all
              select '3.1.2.1'      as version_number from dual union all
              select '3.1.2.100 '   as version_number from dual)
,versions as  (select  trim(version_number) as version_number,
        nvl(LPAD(trim(regexp_substr(version_number, '[^.]+', 1, 1)),5,'0'),'00000') AS Major,
        nvl(LPAD(trim(regexp_substr(version_number, '[^.]+', 1, 2)),5,'0'),'00000') AS Minor, 
        nvl(LPAD(trim(regexp_substr(version_number, '[^.]+', 1, 3)),5,'0'),'00000') AS Revision, 
        nvl(LPAD(trim(regexp_substr(version_number, '[^.]+', 1, 4)),5,'0'),'00000') AS Build 
        from inputs 
        ORDER BY Major desc, Minor desc, Revision desc, Build desc)
--select * from versions; 
select version_number from versions;

Remove the -- to see the intermediate result.

For OP, replace "inputs as (select ... from dual)" with:

   inputs as (select version_number from mytable)

score 0 · Answer 3 · edited May 23 '17 at 11:53

As Joel Coehoorn suggestions here, "refactor version number storage so that each section has it's own column: MajorVersion, MinorVersion, Revision, Build".

I'm re-posting as I found this very helpful!

To expand, I was looking to get the MAX version number, and ended up using this script along with Joel's suggestion.

    -- GET MAX VERSION NUMBER
    SELECT
        REPLACE(vnum, ' ', '') AS versionum
    FROM
        (SELECT
            MAX(LPAD(major, 4) || '.' || LPAD(minor, 4) || '.' || LPAD(revision, 4)) AS vnum
        FROM
            my_table
        ORDER BY
            major
          , minor
          , revision
        ) tbl1

Oracle SQL to Sort Version Numbers

3 Answers3

Linked