‎02-14-2020 05:50 PM
Select Top 1000000 tblAssets.AssetName,
tblSoftware.softwareVersion
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID And
tblSoftwareUni.softwareName Like '%Google Chrome%' And
tblSoftware.softwareVersion >= '80.0.3987.106' And tblAssetCustom.State = 1
‎02-17-2020 04:07 PM
‎02-14-2020 10:45 PM
‎02-14-2020 10:57 PM
RC62N wrote:
Fair enough, and you're definitely right about your scenario. All I can think of offhand would be to expand the slice-and-dice substring extraction to accommodate more than three decimals, but it's already unwieldy handling only three.
Questioner levels here vary from total noob upward, so I was reading your messages at face value. No offense meant in assuming the basics weren't understood.
Good luck.
‎02-14-2020 10:05 PM
80.0.3987.106where "1" is clearly < "8", you would be comparing (I'm inserting decimals for illustration)
80.0.3987.87
00080.00000.03987.00106In that case, the first three pieces would be identical, but by the time the third character of the last piece is compared, "0" (of "087") is clearly less than "1" (of "106").
00080.00000.03987.00087
‎02-14-2020 10:33 PM
RC62N wrote:
SQL Server can't understand that 106 > 87 because it's not seeing numbers; it's comparing the text "106" to the text "87". For all intents and purposes, you're asking it to compare "BAG" to "IH". The version "number" is a text string, so that's how the comparison works: it's a left-to-right text comparison, so right out of the gate "1" < "8".
This is why I suggested zero-padding the sliced-and-diced version "number". Instead of comparing the strings80.0.3987.106where "1" is clearly < "8", you would be comparing (I'm inserting decimals for illustration)
80.0.3987.8700080.00000.03987.00106In that case, the first three pieces would be identical, but by the time the third character of the last piece is compared, "0" (of "087") is clearly less than "1" (of "106").
00080.00000.03987.00087
‎02-14-2020 09:10 PM
‎02-14-2020 08:05 PM
SELECT Right('00000'+VersionA,5) + Right('00000'+VersionB,5) + Right('00000'+VersionC,5) + Right('00000'+VersionD,5)would produce "00080000000398700106". It's not as efficient as a simple numeric comparison, but you could do comparisons on that padded-out string.
‎02-14-2020 06:44 PM
‎02-14-2020 06:25 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now