How to Sort a 'Version Number' Column Generically Using a SQL Server Query

How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query

If You are using SQL Server 2008

select VersionNo from Versions order by cast('/' + replace(VersionNo , '.', '/') + '/' as hierarchyid);

What is hierarchyid

Edit:

Solutions for 2000, 2005, 2008: Solutions to T-SQL Sorting Challenge here.

The challenge

Oracle SQL to Sort Version Numbers

This is one way to do it. First order by the number before . and then by the numbers after .

select version_number 
from mytable 
order by substr(version_number, 1, instr(version_number,'.')-1) desc
        ,length(substr(version_number, instr(version_number,'.')+1)) desc
        ,substr(version_number, instr(version_number,'.')+1) desc

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

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

Sort Release Versions that contain two different decimal places

In C# you could store them as (or at least convert to) Version which also supports correct sorting:

var strings = new List<string> { "14.14", "14.14.4", "14.15", "14.15.1", "14.15.2" };
var versions = strings.Select(v => Version.Parse(v)).ToList();
versions.Sort();  // or OrderBy in the LINQ query above

Here's a question which shows how to sort versions in the database: How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query

SQL WHERE IN (...) sort by order of the list?

Select ID list using subquery and join with it:


select t1.*
from t1
inner join
(
  select 1 as id, 1 as num
  union all select 5, 2
  union all select 3, 3
) ids on t1.id = ids.id
order by ids.num

UPD: Code fixed

how to manipulate string column of numbers separated by dots using t-sql

for future readers: based on @AlexK. link that is the solution:

select TOP 1 VersionNumber from Users order by (cast('/' + replace(VersionNumber , '.', '/') + '/' as hierarchyid)) DESC;