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

http://stackoverflow.com/questions/3474870/how-can-i-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<http://stackoverflow.com/questions/3474870/how-can-i-sort-a-version-number-column-generically-using-a-sql-server-query>

2down votefavorite<http://stackoverflow.com/questions/3474870/how-can-i-sort-a-version-number-column-generically-using-a-sql-server-query>
1
share [g+]share [fb]share [tw]


I wonder if the SQL geniuses amongst us could lend me a helping hand.

I have a column VersionNo in a table Versions that contains 'version number' values like

VersionNo

---------

1.2.3.1

1.10.3.1

1.4.7.2

etc.

I am looking to sort this, but unfortunately, when I do a standard order by, it is treated as a string, so the order comes out as

VersionNo

---------

1.10.3.1

1.2.3.1

1.4.7.2

Intead of the following, which is what I am after:

VersionNo

---------

1.2.3.1

1.4.7.2

1.10.3.1

So, what I need to do is to sort by the numbers in reverse order (e.g. in a.b.c.d, I need to sort by d,c,b,a to get the correct sort ourder).

But I am stuck as to how to achieve this in a GENERIC way. Sure, I can split the string up using the various sql functions (e.g. left, right, substring, len, charindex), but I can't guarantee that there will always be 4 parts to the version number. I may have a list like this:

VersionNo

---------

1.2.3.1

1.3

1.4.7.2

1.7.1

1.10.3.1

1.16.8.0.1

Can, does anyone have any suggestions? Your help would be much appreciated.



Answer:




If You are using SQL Server 2008

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

What is hierarchyid<http://msdn.microsoft.com/en-us/library/bb677290.aspx>

Edit:

Solutions for 2000, 2005, 2008: Solutions to T-SQL Sorting Challenge here<http://www.sqlmag.com/article/sql-server/solutions-to-t-sql-sorting-challenge.aspx>.

The challenge<http://www.sqlmag.com/article/sql-server/t-sql-sorting-challenge.aspx>



Note:

In SQL Server 2008 things are drastically simpler. With slight adjustment of the dot separated list of values, you get the canonical string representation a HIERARCHYID value. Either replace all dots in the string with slashes and add a slash at the beginning and end of the string, or simply add a slash at the beginning and end of the string. Either way, convert the adjusted string to HIERARCHYID, and the values would naturally sort correctly based on the integer values in the string segments. Several people came with such a solution: Razvan Socol, Pawel Potasinski, Peter DeBetta and myself. Here are two examples based on this approach:
select val
from dbo.t1
order by cast('/' + replace(val, '.', '/') + '/' as hierarchyid);


select val
from dbo.t1
order by cast('/' + val + '/' as hierarchyid);