Maximum value of a column-CodePudding

In my Oracle database, I have these values in a column:

6.40.123580

10.10.114580

10.10.114582

I would like to perform a MAX() function on this column, but if I take my example, I never get "10.10.114582". It shows me "6.40.123580".

Is there a solution? Thank you!

CodePudding user response：

Using MAX directly on the string does not work as it compares the values as strings and looks for the greatest first character then the greatest second character, etc. Since 6 (at the start of 6.40.123580) is greater than 1 (at the start of 10.10.11458?) then 6.40.123580 is greater when compared as a string.

What you need to do is split the string on the . character and then compare the parts numerically.

To do this, you can use simple (fast) string functions to find the positions of the . separators and then find the sub-strings between those separators to get the numeric components and then take the row with the greatest values in each successive component:

SELECT version_number,
       TO_NUMBER(SUBSTR(version_number,        1, sep1 -    1)) AS major,
       TO_NUMBER(SUBSTR(version_number, sep1   1, sep2 - sep1)) AS minor,
       TO_NUMBER(SUBSTR(version_number, sep2   1)) AS patch
FROM   (
  SELECT version_number,
         INSTR(version_number,'.',1,1) AS sep1,
         INSTR(version_number,'.',1,2) AS sep2
  FROM   table_name
)
ORDER BY major DESC, minor DESC, patch DESC
FETCH FIRST ROW ONLY;

Or, using (slower) regular expressions:

SELECT version_number,
       TO_NUMBER(REGEXP_SUBSTR(version_number, '\d ', 1, 1)) AS major,
       TO_NUMBER(REGEXP_SUBSTR(version_number, '\d ', 1, 2)) AS minor,
       TO_NUMBER(REGEXP_SUBSTR(version_number, '\d ', 1, 3)) AS patch
FROM   table_name
ORDER BY major DESC, minor DESC, patch DESC
FETCH FIRST ROW ONLY;

Which, for your sample data:

CREATE TABLE table_name (version_number) AS
SELECT  '6.40.123580' FROM DUAL UNION ALL
SELECT '10.10.114580' FROM DUAL UNION ALL
SELECT '10.10.114582' FROM DUAL;

Both output:

VERSION_NUMBER MAJOR MINOR PATCH

10.10.114582 10 10 114582

VERSION_NUMBER	MAJOR	MINOR	PATCH
10.10.114582	10	10	114582

If you particularly want to use the MAX aggregation function then you can use MAX() KEEP () to only keep the greatest of the component parts:

SELECT MAX(version_number) KEEP (
         DENSE_RANK LAST ORDER BY
         TO_NUMBER(SUBSTR(version_number,        1, sep1 -    1)),
         TO_NUMBER(SUBSTR(version_number, sep1   1, sep2 - sep1)),
         TO_NUMBER(SUBSTR(version_number, sep2   1))
       ) AS max_version_number
FROM   (
  SELECT version_number,
         INSTR(version_number,'.',1,1) AS sep1,
         INSTR(version_number,'.',1,2) AS sep2
  FROM   table_name
);

Which, for the sample data, outputs:

MAX_VERSION_NUMBER

10.10.114582

MAX_VERSION_NUMBER
10.10.114582

db<>fiddle here