CODEWITHSUNDEEP
sql-servert-sqlsortingmaxvarchar
Ogre Psalm33
Ogre Psalm33

Reputation: 21946

How to get MAX value of a version-number (varchar) column in T-SQL

I have a table defined like this:

Column:  Version     Message
Type:    varchar(20) varchar(100)
----------------------------------
Row 1:    2.2.6       Message 1
Row 2:    2.2.7       Message 2
Row 3:    2.2.12      Message 3
Row 4:    2.3.9       Message 4
Row 5:    2.3.15      Message 5

I want to write a T-Sql query that will get message for the MAX version number, where the "Version" column represents a software version number. I.e., 2.2.12 is greater than 2.2.7, and 2.3.15 is greater than 2.3.9, etc. Unfortunately, I can't think of an easy way to do that without using CHARINDEX or some complicated other split-like logic. Running this query:

SELECT MAX(Version) FROM my_table

will yield the erroneous result:

2.3.9

When it should really be 2.3.15. Any bright ideas that don't get too complex?

Upvotes: 5

Views: 5516

Answers (2)

Thomas
Thomas

Reputation: 64645

One solution would be to use a table-valued split function to split the versions into rows and then combine them back into columns so that you can do something like:

Select TOP 1 Major, Minor, Build
From ( ...derived crosstab query )
Order By Major Desc, Minor Desc, Build Desc

Actually, another way is to use the PARSENAME function which was meant to split object names:

Select TOP 1 Version
From Table
Order By Cast(Parsename( Z.Version , 3 ) As Int) Desc
    , Cast(Parsename( Z.Version , 2 ) As Int) Desc
    , Cast(Parsename( Z.Version , 1 ) As Int) Desc

Upvotes: 12

Remus Rusanu
Remus Rusanu

Reputation: 294297

Does it have to be efficient on a large table? I suggest you create an indexed persisted computed column that transform the version into a format that ranks correctly, and use the computed column in your queries. Otherwise you'll always scan end to end.

If the table is small, it doesn't matter. Then you can use a just-in-time ranking, using a split function, or (ab)using the parsename as Thomas suggested.

Upvotes: 3

Related Questions