MAX value with multiple decimal points

I have a table that stores the versions of software installed on a client. I need to return the highest version value. MAX(versionValue) does not return the highest value because the value is in the format like 2.3.0.99. If the highest value is 2.3.0.122 MAX ret开发者_Python百科urns 2.3.0.99 as the highest value, which is not true. I'm sure this is pretty trivial, I've just hit the wall and Google is no help right now. Any insight would be appreciated.

If you can change the database schema and assuming the version always follows the same format you could add four new columns for

Major, Minor, Build, Revision

These would then all be ints you could then either user string concatination to give the version number 2.3.0.122 or use arithmatic to get the max version for example

(Major * 1000) + (Minor * 100) + (Build * 10) + Revision

Only issue with that would be it needs to do a full table scan to find out what the highest is and there could be issues where a minor release is so high that it over takes a major release.

But im sure you get the idea.

The problem you are having is because you are storing 4 pieces of information in one column and now need to decompose into the constituent pieces.

Ideally you should put your table into 1NF i.e. split these up into 4 int columns (and then put a composite index on them).

You could have a (non persisted) computed column that concatenates them for display purposes.

If that isn't possible the following is not sargable but does the job.

DECLARE @versions TABLE (
  version_number varchar(15),
  productid      int )

INSERT INTO @versions
SELECT '2.3.0.122',1 UNION ALL 
SELECT '2.3.0.99',1 UNION ALL 
SELECT '2.3.0.122',2 UNION ALL 
SELECT '2.4.0.99',2;

WITH cte
     AS (select productid,
                version_number,
                ROW_NUMBER() over (partition by productid 
                                       order by CAST('/' +
                   version_number + '/' AS hierarchyid) desc) AS RN
         FROM   @versions)
SELECT productid,
       version_number
FROM   cte
WHERE  RN = 1  

Thanks everyone. I ended up creating a UDF in CLR. The DB is associated with a 3rd Party Product and so reconstructing the schema was not an option.