July 27, 2011 at 7:52 am
Dear All,
I have two values to compare as below.
1) 10.1.85.3366
2) 10.1.102.64
Both are some application versions. We know that version "10.1.102.64" is the higher version.
Is there any way to compare and find the higher one using query.?
I tried by keeping the first decimal but it doesn't work for the values like above.
Can anyone help pls...
Thanks in advance.
July 27, 2011 at 8:16 am
Joy the only way i thought to do this right was with a case statement...i ended up dumping it into a scalr function;
you can use this as an example and change the return values, but i was returning 1 if the first param was greater, 2 if the second param was greater, and 0 if they were equal.
/*
select dbo.prCompareVersions( '10.1.85.3366','10.1.102.64')
*/
Create Function prCompareVersions(@FirstIP varchar(20),@SecondIP varchar(20))
RETURNS int --0 means equal, 1 for 1st , 2 for second?
AS
BEGIN --FUNCTION
DECLARE @Results int
WITH MyCTE AS (
SELECT CONVERT(int,parsename(@FirstIP,4)) As IP1_4,
CONVERT(int,parsename(@FirstIP,3)) As IP1_3,
CONVERT(int,parsename(@FirstIP,2)) As IP1_2,
CONVERT(int,parsename(@FirstIP,1)) As IP1_1,
CONVERT(int,parsename(@SecondIP,4)) As IP2_4,
CONVERT(int,parsename(@SecondIP,3)) As IP2_3,
CONVERT(int,parsename(@SecondIP,2)) As IP2_2,
CONVERT(int,parsename(@SecondIP,1)) As IP2_1 )
SELECT
@Results = CASE
WHEN IP1_4 <> IP2_4
THEN CASE WHEN IP1_4 > IP2_4 THEN 1 ELSE 2 END
WHEN IP1_3 <> IP2_3
THEN CASE WHEN IP1_3 > IP2_3 THEN 1 ELSE 2 END
WHEN IP1_2 <> IP2_2
THEN CASE WHEN IP1_2 > IP2_2 THEN 1 ELSE 2 END
WHEN IP1_1 <> IP2_1
THEN CASE WHEN IP1_3 > IP2_3 THEN 1 ELSE 2 END
ELSE 0
END
FROM MyCTE
RETURN @Results
END --FUNCTION
Lowell
July 27, 2011 at 8:33 am
Thanks for your quick response.
It wont work for me.
There can be 'n' number of versions, 'n' number of applications and millions of rows. Also the version can be in any format like, 1 or 1.0 or 1.0.10.2...... anything...
I was trying to use "Rownumber" function partition by each application, with version in DESC order. So that I can blindly delete all those rows whose rowid is > 1.
Used the below code...
row_number() over(partition by applicationname order by cast(replace(substring(pversion, 1, charindex('.', pversion))+''+replace(substring(pversion, charindex('.', pversion), 15), '.', ''), '_', '' ) as float) desc) as rid
It works fine for one decimal, also if the number of charecters are equal after each decimal point. But if there is a diff it fails.
For example it works just fine for the values "10.1.8.53" and "10.1.9.53"
and will fail for "10.1.8.53" and "10.1.10.53"..... '8' is taken as greater than '10' ....
I got lost....
Thanks.
July 27, 2011 at 8:43 am
yeah, you should test drive mine first before you say it won't work....
whaty would you expect to be greater if you compare Version "10" to version '1.4.56.2344'?
if everytthing is 4 part convention,
mine takes that comparison structure into consideration; by converting to integers, you resolve that comparison issue you are hitting where you compare 3 digit text to 2 digit text
if you can show the schema you are trying to compare, i could convert it to a table value function, to make it more efficient for huge # of rows
but my function would work right now for something like this:
--find where Version1 is greater than version 2:
SELECT
T1.PK,
T1.Version1,
T2.Version2
from SomeTable T1
inner join AnotherTable T2 On T1.PK = T2.PK
where dbo.prCompareVersions(T1Version1,T2Version2) = 1
Lowell
July 27, 2011 at 8:57 am
How about ....
;WITH sampleData (appID, appVersion)
AS (SELECT 1, '10.1.55.3366'
UNION
SELECT 1, '10.1.99.64'
UNION
SELECT 1, '10.1.1.1'
UNION
SELECT 1, '10.1.3.9'
UNION
SELECT 1, '9999.9999.9999.9999'
UNION
SELECT 2, '7.0.4.1'
UNION
SELECT 2, '7.1.5.3')
,versionData
AS (SELECT
appID
,appVersion
,CAST(PARSENAME(appVersion,4) AS BIGINT) * 10000 * 10000 * 10000 +
CAST(PARSENAME(appVersion,3) AS BIGINT) * 10000 * 10000 +
CAST(PARSENAME(appVersion,2) AS BIGINT) * 10000 +
CAST(PARSENAME(appVersion,1) AS BIGINT) as versionNumber
FROM
sampleData)
SELECT
appID
,appVersion
,versionNumber
,ROW_NUMBER() OVER (PARTITION BY appID ORDER BY versionNumber DESC) as x
FROM
versionData
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 27, 2011 at 9:04 am
Nice, Jason, i like that...
I think she has data that is not in 4 part naming conventions tho...version 10.45 compares to 8.34.53.2234 for example....i'm adding a CTE to my example and testing that now.
--edit: this seems to take that logic into account:
ALTER Function prCompareVersions(@FirstIP varchar(20),@SecondIP varchar(20))
RETURNS int --0 means equal, 1 for 1st , 2 for second?
AS
BEGIN --FUNCTION
DECLARE @Results int,
@Ip1 varchar(50),
@Ip2 varchar(50)
SET @Ip1 = @FirstIP + '.0.0.0.0'
SET @Ip2 = @SecondIP + '.0.0.0.0'
WITH CleanupMissingVersionDetails
AS
--if '10', is passed, converts to 10.0.0.0 format.
--if 10.4 is passed, converts to 10.4.0.0 format, etc
(SELECT
FirstIP = SUBSTRING(@Ip1,1,dbo.charindex2('.',@Ip1,4) -1),
SecondIP = SUBSTRING(@Ip2,1,dbo.charindex2('.',@Ip2,4) -1)
),MyCTE AS (
SELECT CONVERT(int,parsename(FirstIP,4)) As IP1_4,
CONVERT(int,parsename(FirstIP,3)) As IP1_3,
CONVERT(int,parsename(FirstIP,2)) As IP1_2,
CONVERT(int,parsename(FirstIP,1)) As IP1_1,
CONVERT(int,parsename(SecondIP,4)) As IP2_4,
CONVERT(int,parsename(SecondIP,3)) As IP2_3,
CONVERT(int,parsename(SecondIP,2)) As IP2_2,
CONVERT(int,parsename(SecondIP,4)) As IP2_1
FROM CleanupMissingVersionDetails)
SELECT
@Results = CASE
WHEN IP1_4 <> IP2_4
THEN CASE WHEN IP1_4 > IP2_4 THEN 1 ELSE 2 END
WHEN IP1_3 <> IP2_3
THEN CASE WHEN IP1_3 > IP2_3 THEN 1 ELSE 2 END
WHEN IP1_2 <> IP2_2
THEN CASE WHEN IP1_2 > IP2_2 THEN 1 ELSE 2 END
WHEN IP1_1 <> IP2_1
THEN CASE WHEN IP1_3 > IP2_3 THEN 1 ELSE 2 END
ELSE 0
END
FROM MyCTE
RETURN @Results
END --FUNCTION
and if you need the charindex2 function:
/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION CHARINDEX2(
@TargetStr varchar(8000),
@SearchedStr varchar(8000),
@Occurrence int)
RETURNS int
AS
BEGIN
DECLARE @pos int, @counter int, @ret int
SET @pos = CHARINDEX(@TargetStr, @SearchedStr)
SET @counter = 1
IF @Occurrence = 1
SET @ret = @pos
ELSE
BEGIN
WHILE (@counter < @Occurrence)
BEGIN
SELECT @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
SET @counter = @counter + 1
SET @pos = @ret
END
END
RETURN(@ret)
END
GO
Lowell
July 27, 2011 at 10:20 am
Here's one that doesn't use a WHILE loop, and should work a version component of any size (up to the int limit). It does assume that there will be no more than four version components. Thanks to Jeff Moden for his splitter function.
John
(Code is in text file since it didn't work when I tried to post it in line)
July 27, 2011 at 10:43 am
No While Loops, No functions, No tally tables ... 😀
Works with any number of version "dots" up to 4
;WITH sampleData (appID, appVersion)
AS (SELECT 1, '10.1.55.3366'
UNION
SELECT 1, '10.1.99.64'
UNION
SELECT 1, '10.1.1.1'
UNION
SELECT 1, '10.1.3.9'
UNION
SELECT 3, '9999.9999.9999.9999'
UNION
SELECT 2, '7.0.4'
UNION
SELECT 2, '7.1'
UNION
SELECT 2, '8'
UNION
SELECT 2, '7.1.0.3')
,versionData
AS (SELECT
appID
,REVERSE(appVersion) as appVersion
,ISNULL(CAST(PARSENAME(REVERSE(appVersion),1) AS BIGINT),0) * 10000 * 10000 * 10000 +
ISNULL(CAST(PARSENAME(REVERSE(appVersion),2) AS BIGINT),0) * 10000 * 10000 +
ISNULL(CAST(PARSENAME(REVERSE(appVersion),3) AS BIGINT),0) * 10000 +
ISNULL(CAST(PARSENAME(REVERSE(appVersion),4) AS BIGINT),0) as versionNumber
FROM
sampleData)
SELECT
appID
,REVERSE(appVersion) as appVersion
,versionNumber
,ROW_NUMBER() OVER (PARTITION BY appID ORDER BY versionNumber DESC) as x
FROM
versionData
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 27, 2011 at 10:45 am
^-- I added in the REVERSE function to help with the possibility of not have four sections.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 28, 2011 at 1:34 am
This is wonderful.... It might work for me definitely...
Will try and get back...
Thanks to all ......
July 28, 2011 at 2:00 am
Jason, your code will no doubt work in most circumstances, but consider the following sample data - it produces strange results:
SELECT 1, '1000000.1.55.3366'
UNION
SELECT 1, '10.1000000.99.64'
UNION
SELECT 1, '10.1.1000000.1'
UNION
SELECT 1, '10.1.3.9000000'
UNION
SELECT 3, '9999000.9999.9999.9999'
UNION
SELECT 2, '7.0.4'
UNION
SELECT 2, '7.1'
UNION
SELECT 2, '8'
UNION
SELECT 2, '7.1.0.3'
I don't know how we'll get on when we have a large table of apps and versions. I've a feeling that the tally table is going to be quicker than the string manipulations, but I haven't had time to prove that.
John
July 28, 2011 at 4:30 am
Hello....
One issue.. It dint work for the following values...
9.0.124.0
10.0.22.87
Any idea..?
Thanks.
July 28, 2011 at 4:47 am
What didn't? You've been provided with at least two solutions. Please post an error message if there was one, otherwise sample data and expected results.
John
July 28, 2011 at 5:11 am
try the below code...
create table sampleData (appID int, appVersion varchar(100))
------
insert into sampleData
SELECT 1, '10.1.55.3366'
UNION
SELECT 1, '10.1.99.64'
UNION
SELECT 1, '10.1.1.1'
UNION
SELECT 1, '10.1.3.9'
UNION
SELECT 1, '9999.9999.9999.9999'
UNION
SELECT 1, '9.0.124.0'
-----------------------------
SELECT appID
, REVERSE(appVersion) as appVersion
, versionNumber
, ROW_NUMBER() OVER (PARTITION BY appID ORDER BY versionNumber DESC) as x
FROM(SELECTappID
,REVERSE(appVersion) as appVersion
,ISNULL(CAST(PARSENAME(REVERSE(appVersion),1) AS BIGINT),0) * 10000 * 10000 * 10000 +
ISNULL(CAST(PARSENAME(REVERSE(appVersion),2) AS BIGINT),0) * 10000 * 10000 +
ISNULL(CAST(PARSENAME(REVERSE(appVersion),3) AS BIGINT),0) * 10000 +
ISNULL(CAST(PARSENAME(REVERSE(appVersion),4) AS BIGINT),0) as versionNumber
FROMsampleData
)versionData
----------------------
July 29, 2011 at 4:10 am
Joy Smith San (7/28/2011)
try the below code...create table sampleData (appID int, appVersion varchar(100))
------
insert into sampleData
SELECT 1, '10.1.55.3366'
UNION
SELECT 1, '10.1.99.64'
UNION
SELECT 1, '10.1.1.1'
UNION
SELECT 1, '10.1.3.9'
UNION
SELECT 1, '9999.9999.9999.9999'
UNION
SELECT 1, '9.0.124.0'
-----------------------------
SELECT appID
, REVERSE(appVersion) as appVersion
, versionNumber
, ROW_NUMBER() OVER (PARTITION BY appID ORDER BY versionNumber DESC) as x
FROM(SELECTappID
,REVERSE(appVersion) as appVersion
,ISNULL(CAST(PARSENAME(REVERSE(appVersion),1) AS BIGINT),0) * 10000 * 10000 * 10000 +
ISNULL(CAST(PARSENAME(REVERSE(appVersion),2) AS BIGINT),0) * 10000 * 10000 +
ISNULL(CAST(PARSENAME(REVERSE(appVersion),3) AS BIGINT),0) * 10000 +
ISNULL(CAST(PARSENAME(REVERSE(appVersion),4) AS BIGINT),0) as versionNumber
FROMsampleData
)versionData
----------------------
9.0.124.0 is coming greater than 10 .
Any solution from this..???
Thanks.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply