Comparinng integer like string values.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  July 29, 2011 at 4:15 am

  #1360638

  You could wait for Jason to come back to you... in the meantime, have you tried my solution?

  John

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 29, 2011 at 6:13 am

  #1360707

  This shows some promise:

  SELECT d.MyKey, d.AppVersion, x1.OrderSeq

  FROM (

  SELECT MyKey = 1, AppVersion = '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'

  ) d

  CROSS APPLY (SELECT OrderSeq = (

  SELECT RIGHT('00000000' + Item,8) + ','

  FROM dbo.DelimitedSplit8K (AppVersion,'.')

  ORDER BY ItemNumber FOR XML PATH('')

  )) x1

  ORDER BY x1.OrderSeq DESC

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  July 29, 2011 at 6:47 am

  #1360725

  John Mitchell-245523 (7/29/2011)

  ---

  You could wait for Jason to come back to you... in the meantime, have you tried my solution?

  John

  Thanks.

  Problem is we can't create function or any other objects in client's DB. Have to manage with select query itself...

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  July 29, 2011 at 6:54 am

  #1360733

  Joy Smith San (7/29/2011)

  ---

  Problem is we can't create function or any other objects in client's DB.

  If this is a one-off, you can create the function in tempdb. Or maybe you have an admin database or something like that on the server. Can even put it in master.

  John

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 29, 2011 at 7:05 am

  #1360746

  Joy Smith San (7/29/2011)

  ---

  ...

  Problem is we can't create function or any other objects in client's DB. Have to manage with select query itself...

  No problem.

  SELECT d.MyKey, d.AppVersion, x1.*

  FROM (

  SELECT MyKey = 1, AppVersion = '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'

  ) d

  CROSS APPLY (

  SELECT OrderSeq = (SELECT RIGHT('00000000' + Item,8) + ','

  FROM (

  SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),

  Item = SUBSTRING(d.AppVersion,s.N1,ISNULL(NULLIF(CHARINDEX('.',d.AppVersion,s.N1),0)-s.N1,8000))

  FROM (

  SELECT n1 = t.N +1

  FROM (SELECT n = ROW_NUMBER() OVER(ORDER BY [Name])-1 FROM sys.COLUMNS) t

  WHERE SUBSTRING(d.AppVersion,t.N,1) = '.' OR t.N = 0

  ) s

  ) del

  ORDER BY ItemNumber FOR XML PATH(''))

  ) x1

  ORDER BY x1.OrderSeq DESC

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• janis.l.murphy

  SSC Veteran

  Points: 239

  More actions

  July 29, 2011 at 8:54 am

  #1360852

  I found I needed to tweak it a bit. I needed to add an extra REVERSE() to each versionNumber element.

  ;WITH sampleData (appID, appVersion)

  AS (SELECT 1, '10.1.55.3366'

  UNION

  SELECT 1, '10.1.99.64'

  UNION

  SELECT 1, '10.1.100.1' -- Note change of third part

  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(REVERSE(PARSENAME(REVERSE(appVersion),1)) AS BIGINT),0) * 10000 * 10000 * 10000 +

  ISNULL(CAST(REVERSE(PARSENAME(REVERSE(appVersion),2)) AS BIGINT),0) * 10000 * 10000 +

  ISNULL(CAST(REVERSE(PARSENAME(REVERSE(appVersion),3)) AS BIGINT),0) * 10000 +

  ISNULL(CAST(REVERSE(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

  order by appID, x