As is often the case, especially with SCCM, version numbers of applications and files are stored as strings (i.e., '7.0.90'). Depending on the software publisher, the version number may or may not be easily sortable in SQL Server. I've created a function that can be used in an ORDER BY clause to facilitate sorting of version numbers.

Normally, in SQL Server, given the versions 7.0.90 and 7.0.110, SQL will list 7.0.110 first because it sorts it alphanumerically, as a string. It doesn't understand that 7.0.110 comes after 7.0.90 as a version number (build 90 vs. build 110). In order to allow this to work, I've create a simple function that breaks apart the version number on the period and changes each section into a block exactly 10 characters long before putting it back together for use in the normal sorting comparison.

7.0.90 becomes:

         7.         0.        90

7.0.110 becomes:

         7.         0.       110

Since a space comes before the numeral 1 when sorting a string, the .90 version is displayed first.

It should be noted that this function requires a period (.) to be the separator between portions of the version string. If the string has commas or spaces, use a REPLACE function to revert it to periods.

To use the function, you don't need to include it in the SELECT clause, just the ORDER BY portion.

--Example: SELECT Version0 from v_GS_SOME_TABLE ORDER BY dbo.SC_SORTABLEVERSIONSTRING(Version0)

CREATE FUNCTION [dbo].[SC_SORTABLEVERSIONSTRING] ( @RAWVER nvarchar(128) ) RETURNS nvarchar(128) AS
BEGIN
    DECLARE @FMTED nvarchar(128), @PORTION nvarchar(128)
    DECLARE @CTR int
    
    --Initialize our portion counter and our built out string
    SELECT @CTR = 1
    SELECT @FMTED = ''
    
    --Use the PARSENAME built-in function to obtain the section of the
    --version string identified by @CTR.  PARSENAME uses the period character
    --as the separator.  PARSENAME also returns items in reverse order.
    --So, item 1 is the last (least significant; right-most) portion.
    SELECT @PORTION = PARSENAME(@RAWVER,@CTR)
    
    --Loop through until we have no more data to loop through
    WHILE @PORTION<>''
    BEGIN
        --Append a 10 character block to the beginning of our final string
        SELECT @FMTED = SPACE(10-LEN(@PORTION)) + @PORTION + '.' + @FMTED
        --Increment the counter
        SELECT @CTR = @CTR + 1
        --Get the next portion of the version string
        SELECT @PORTION = PARSENAME(@RAWVER,@CTR)
    END
    --Return our formatted string
    RETURN @FMTED
END