How to sort version numbers (like 5.3.60.8)

Question

I have a Strings like:

5.3.60.8 
6.0.5.94
3.3.4.1

How to sort these values in sorting order in Oracle SQL?

I want the order to be like this:

6.0.5.94
5.3.60.8
3.3.4.1

What is the "sorting order"? First by the first number, then by the second, etc.? — mathguy, Jul 25 '16 at 13:48

mathguy · Answer 1 · 2016-07-25T14:02:04.917

3

with
     inputs ( str ) as (
       select '6.0.5.94' from dual union all
       select '5.3.60.8' from dual union all
       select '3.3.4.1'  from dual
     )
select str from inputs
order by to_number(regexp_substr(str, '\d+', 1, 1)), 
         to_number(regexp_substr(str, '\d+', 1, 2)),
         to_number(regexp_substr(str, '\d+', 1, 3)),
         to_number(regexp_substr(str, '\d+', 1, 4))
;

STR
--------
3.3.4.1
5.3.60.8
6.0.5.94

edited Jul 25 '16 at 14:02

answered Jul 25 '16 at 13:54

mathguy

37,873
5
22
47

2

I suppose that you should use to_number in order by, if you don't want 30 to be before 4. – Mottor Jul 25 '16 at 13:56
Darn - I meant to do that and I forgot! I will edit to fix that, it absolutely is needed. Thanks! – mathguy Jul 25 '16 at 14:01
But here I have not only 3 values....I have more than 200 strings...in this case how to get it from table name called versions? – Krishna Nagidi Jul 25 '16 at 14:03
@KrishnaNagidi Use your table name instead of 'inputs' and your column name instead of 'str' – Mottor Jul 25 '16 at 14:09
@KrishnaNagidi ... what Mottor said, and in addition, you don't need the first six rows; your query will be `select ... ` where the first word after `select` will not be `str` but the name of the column in your table that holds the values, and `inputs` is replaced by your table name (perhaps `versions` if that is what you meant). DON'T FORGET to replace `str` with your actual column name in the call to regexp_substr() in the ORDER BY clause too - four substitutions there and five total for `str`. – mathguy Jul 25 '16 at 15:27
I did the changes..now its working perfectly..Thank you very much for your kind support – Krishna Nagidi Jul 25 '16 at 15:49
Glad it worked! If any of the answers posted here helped solve your problem, you should mark the question as "answered" so volunteers on the forum can concentrate on unanswered questions. Thanks! – mathguy Jul 26 '16 at 12:30

score 1 · Answer 2 · edited May 23 '17 at 10:29

1

You could pad numbers with zeroes on the left in the order by clause:

select   version
from     versions
order by regexp_replace(
            regexp_replace(version, '(\d+)', lpad('\1', 11, '0')),
            '\d+(\d{10})',
            '\1'
         ) desc

This works for more number parts as well, up to about 200 of them.

If you expect to have numbers with more than 10 digits, increase the number passed as second argument to the lpad function, and also the braced number in the second regular expression. The first should be one more (because \1 is two characters but could represent only one digit).

Highest version

To get the highest version only, you can add the row number to the query above with the special Oracle rownum keyword. Then wrap all that in an another select with a condition on that row number:

 select version
 from (
      select   version, rownum as row_num
      from     versions
      order by regexp_replace(
                  regexp_replace(version, '(\d+)', lpad('\1', 11, '0')),
                  '\d+(\d{10})',
                  '\1'
               ) desc)
where row_num <= 1;

See this Q&A for several alternatives, also depending on your Oracle version.

edited May 23 '17 at 10:29

Community

1
1

answered Jul 25 '16 at 14:56

trincot

211,288
25
175
211

Modern is modern ;) – Mottor Jul 25 '16 at 15:48
Hi trincot, thank you...How to get highest version from versions?. – Krishna Nagidi Jul 27 '16 at 10:33
I added a reply to that in my answer. – trincot Jul 27 '16 at 12:59
I have data like as below: COMPANY Project Version XXX RBI 8.16.1.1 XXX RBI 8.16.1.2 XXX TAP 38.14.2.10 XXX TAP 38.14.2.9 XXX RB 8.0.4.185 XXX RB 8.0.4.174 need to get highest version from each project.? – Krishna Nagidi Jul 27 '16 at 16:24
The comments section is not suitable for follow-up questions. Please ask a new question. I will be happy to answer if you refer me to it. – trincot Jul 27 '16 at 17:27

score 0 · Accepted Answer · answered Jul 25 '16 at 14:12

I will show here the answer from AskTom, which can be used with different version size :

WITH inputs 
     AS (SELECT 1 as id, '6.0.5.94' as col FROM DUAL
         UNION ALL
         SELECT 2,'5.3.30.8' FROM DUAL
         UNION ALL
         SELECT 3,'5.3.4.8' FROM DUAL
         UNION ALL
         SELECT 4,'3' FROM DUAL
         UNION ALL
         SELECT 5,'3.3.40' FROM DUAL
         UNION ALL
         SELECT 6,'3.3.4.1.5' FROM DUAL
         UNION ALL
         SELECT 7,'3.3.4.1' FROM DUAL)
    SELECT col, MAX (SYS_CONNECT_BY_PATH (v, '.')) p
      FROM (SELECT t.col, TO_NUMBER (SUBSTR (x.COLUMN_VALUE, 1, 5)) r, SUBSTR (x.COLUMN_VALUE, 6) v, id rid
              FROM inputs t,
                   TABLE (
                      CAST (
                         MULTISET (
                                SELECT    TO_CHAR (LEVEL, 'fm00000')
                                       || TO_CHAR (TO_NUMBER (SUBSTR ('.' || col || '.', INSTR ('.' || col || '.', '.', 1, ROWNUM) + 1, INSTR ('.' || col || '.', '.', 1, ROWNUM + 1) - INSTR ('.' || col || '.', '.', 1, ROWNUM) - 1)), 'fm0000000000')
                                  FROM DUAL
                            CONNECT BY LEVEL <= LENGTH (col) - LENGTH (REPLACE (col, '.', '')) + 1) AS SYS.odciVarchar2List)) x)
START WITH r = 1
CONNECT BY PRIOR rid = rid AND PRIOR r + 1 = r
  GROUP BY col
  ORDER BY p

How to sort version numbers (like 5.3.60.8)

3 Answers3

Highest version