Find the best version
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
I have a table like this:
Table1:
Name Null Type
----------------- -------- -------------
NAME NOT NULL VARCHAR2(64)
VERSION NOT NULL VARCHAR2(64)
Table1
Name Version
---------------
A 1
B 12.1.0.2
B 8.2.1.2
B 12.0.0
C 11.1.2
C 11.01.05
I want the result to be like this:
Name Version
---------------
A 1
B 12.1.0.2
C 11.01.05
Basically, I want to get a string for each name that has the highest version. To do this, I use the following query:
SELECT t1.NAME,
t1.VERSION
FROM TABLE1 t1
LEFT OUTER JOIN TABLE1 t2
on (t1.NAME = t2.NAME and t1.VERSION < t2.VERSION)
where t2.NAME is null
Now 't1.VERSION <t2.VERSION' only works in normal versions, but in cases like:
B 12.1.0.2
B 8.2.1.2
It doesn't work, I need a PL / SQL script to normalize the version strings and compare for a higher value.
source to share
Just wrote a custom MySQL function to perform a task, you can easily port it to ORACLE PL / SQL.
DELIMITER $$
DROP FUNCTION IF EXISTS `VerCmp`$$
CREATE FUNCTION VerCmp (VerX VARCHAR(64), VerY VARCHAR(64), Delim CHAR(1))
RETURNS INT DETERMINISTIC
BEGIN
DECLARE idx INT UNSIGNED DEFAULT 1;
DECLARE xVer INT DEFAULT 0;
DECLARE yVer INT DEFAULT 0;
DECLARE xCount INT UNSIGNED DEFAULT 0;
DECLARE yCount INT UNSIGNED DEFAULT 0;
DECLARE counter INT UNSIGNED DEFAULT 0;
SET xCount = LENGTH(VerX) - LENGTH(REPLACE(VerX, Delim,'')) +1;
SET yCount = LENGTH(VerY) - LENGTH(REPLACE(VerY, Delim,'')) +1;
IF xCount > yCount THEN
SET counter = xCount;
ELSE
SET counter = yCount;
END IF;
WHILE (idx <= counter) DO
IF (xCount >= idx) THEN
SET xVer = SUBSTRING_INDEX(SUBSTRING_INDEX(VerX, Delim, idx), Delim, -1) +0;
ELSE
SET xVer =0;
END IF;
IF (yCount >= idx) THEN
SET yVer = SUBSTRING_INDEX(SUBSTRING_INDEX(VerY, Delim, idx), Delim, -1) +0;
ELSE
SET yVer = 0;
END IF;
IF (xVer > yVer) THEN
RETURN 1;
ELSEIF (xVer < yVer) THEN
RETURN -1;
END IF;
SET idx = idx +1;
END WHILE;
RETURN 0;
END$$;
DELIMITER ;
Several tests I ran:
select vercmp('5.2.4','5.2.5','.');
+------------------------------+
| vercmp('5.2.4','5.2.5','.') |
+------------------------------+
| -1 |
+------------------------------+
select vercmp('5.2.4','5.2.4','.');
+------------------------------+
| vercmp('5.2.4','5.2.4','.') |
+------------------------------+
| 0 |
+------------------------------+
select vercmp('5.2.4','5.2','.');
+----------------------------+
| vercmp('5.2.4','5.2','.') |
+----------------------------+
| 1 |
+----------------------------+
select vercmp('1,2,4','5,2',',');
+----------------------------+
| vercmp('1,2,4','5,2',',') |
+----------------------------+
| -1 |
+----------------------------+
source to share
You can do it judiciously using REGEXP_SUBSTR () ; no need to use PL / SQL.
select *
from ( select a.*
, row_number() over (
partition by name
order by to_number(regexp_substr(version, '[^.]+', 1)) desc
, to_number(regexp_substr(version, '[^.]+', 2)) desc
, to_number(regexp_substr(version, '[^.]+', 3)) desc
, to_number(regexp_substr(version, '[^.]+', 4)) desc
) as rnum
from table1 a )
where rnum = 1
Here's a SQL Fiddle to demonstrate. Note how I had to convert each part to a number to get this to work.
However, I can't stress enough how much easier your life will be if you split them into different columns, major version, minor version, etc. Then you can have a virtual column that concatenates them all together to ensure that your exports are always standardized if you like.
If, for example, you created a table like this:
create table table1 (
name varchar2(64)
, major number
, minor number
, build number
, revision number
, version varchar2(200) generated always as (
to_char(major) || '.' ||
to_char(minor) || '.' ||
to_char(build) || '.' ||
to_char(revision)
)
Your request becomes clearer; also in SQL Fiddle
select name, version
from ( select a.*
, row_number() over (
partition by name
order by major desc
, minor desc
, build desc
, revision desc ) as rnum
from table1 a )
where rnum = 1
source to share
This solution is independent of the number of numeric parts within the version code.
Each numerical part is assumed to be no more than 6 digits.
select
name,
max(version) keep (dense_rank first order by version_norm desc)
as max_version
from (
select
t.*,
regexp_replace(
regexp_replace('000000'||version, '\.', '.000000')||'.',
'\d*(\d{6}\.)', '\1')
as version_norm
from table1 t
)
group by name
source to share
You somehow need to convert the string values to numeric values and then scale them with the appropriate multiplier. Let's assume each version value should be a number between 0..99 as an example. So if your string was "8.2.1.2" you would scale the numeric values of the string "abcd" = d + c * 100 + b * 10000 + a * 1000000, = 2 + 100 + 20000 + 8000000 = 8020102, you can use this is the value for the order.
I found a function that you can use to parse a token from a delimited string:
CREATE OR REPLACE FUNCTION get_token (the_list VARCHAR2,
the_index NUMBER,
delim VARCHAR2 := ',')
RETURN VARCHAR2
IS
start_pos NUMBER;
end_pos NUMBER;
BEGIN
IF the_index = 1
THEN
start_pos := 1;
ELSE
start_pos :=
INSTR (the_list,
delim,
1,
the_index - 1);
IF start_pos = 0
THEN
RETURN NULL;
ELSE
start_pos := start_pos + LENGTH (delim);
END IF;
END IF;
end_pos :=
INSTR (the_list,
delim,
start_pos,
1);
IF end_pos = 0
THEN
RETURN SUBSTR (the_list, start_pos);
ELSE
RETURN SUBSTR (the_list, start_pos, end_pos - start_pos);
END IF;
END get_token;
so call something like
select to_number(get_token(version,1,'.'))*1000000 + to_number(get_token(version,2,'.'))*10000 + .. etc.
source to share