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

Question

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.

Davey

In the last case, how do you want it sorted given you don't have 4 things to sort on? – Mossback

As it appears. That is my problem, if it were fixed it would be a breeze. – Davey

Answer 1

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

Metalline

Would that it were. But I'm stuck with old SQL 2000, unfortunately. – Davey

@James Wiseman: In the link You should found the solution for You. – Cloyd

@James - Not sure what you ended up using. You can also use parsename for this as in this answer #3058032 – Suspense

@Martin: I actually used this, but I like your suggestion as well. Thanks. – Davey

It seems that it is not required to replace the dots. You may use this: select * from Versions order by cast('/' + VersionNo + '/' as hierarchyid); – Whyte

Answer 2

Depending on SQL engine for MySQL would be sth like this:

SELECT versionNo FROM Versions
ORDER BY
SUBSTRING_INDEX(versionNo, '.', 1) + 0,
SUBSTRING_INDEX(SUBSTRING_INDEX(versionNo, '.', -3), '.', 1) + 0,
SUBSTRING_INDEX(SUBSTRING_INDEX(versionNo, '.', -2), '.', 1) + 0,
SUBSTRING_INDEX(versionNo, '.', -1) + 0;

For MySQL version 3.23.15 an above

SELECT versionNo FROM Versions ORDER BY INET_ATON(ip);

Interchangeable

Its SQL Server as the tag indicates, but have updated my question accordingly. – Davey

Finally a solution that works, thanks! Note that INET_ATON doesn't work well if the numbers aren't all below 256. – Polytrophic

Answer 3

If you can, alter the schema so that the version has 4 columns instead of one. Then sorting is easy.

Mesoderm

Haha, yeah, that occurred to me, unfortunately, that is not an option. Also, the length may be variable, and I can't guarantee the maximum number of columns will 4, or anything else, really. – Davey

If you're using SQL Server 2005 (may be 2008, need to double-check) or newer, you can write a .NET CLR method to do the comparison, or better yet, transform an arbitrary version number into a canonical, sortable format (e.g. pad every part to, say, 5 digits); register this CLR method with your DB and you can call it like a regular SQL function. Otherwise, you're stuck with implementing the same thing in SQL, which is probably bad for your performance. – Mesoderm

Yeah, unfortunately its an old in-house app in VB6 and SQL 2000. – Davey

Answer 4

Another way to do it:

Assuming you only have a,b,c,d only you may as well separate the data out to columns and do an order by a,b,c,d(all desc) and get the top 1 row

If you need to scale to more than d to say e,f,g... just change 1,2,3,4, to 1,2,3,4,5,6,7 and so on in the query

Query : see demo

create table t (versionnumber varchar(255))
insert into t values
('1.0.0.505')
,('1.0.0.506')
,('1.0.0.507')
,('1.0.0.508')
,('1.0.0.509')
,('1.0.1.2')


; with cte as 
(
    select 
    column1=row_number() over (order by (select NULL)) ,
    column2=versionnumber
    from t
    )

select top 1
    CONCAT([1],'.',[2],'.',[3],'.',[4])
from 
(
    select 
        t.column1,
        split_values=SUBSTRING( t.column2, t1.N, ISNULL(NULLIF(CHARINDEX('.',t.column2,t1.N),0)-t1.N,8000)),
        r= row_number() over( partition by column1 order by t1.N) 
    from cte t 
        join
        (
            select 
                t.column2,
                1 as N 
            from cte t  
                UNION ALL
            select 
                t.column2,
                t1.N + 1 as N
            from cte t 
                join
                (
                 select 
                    top 8000
                        row_number() over(order by (select NULL)) as N 
                 from 
                    sys.objects s1 
                        cross join 
                   sys.objects s2 
                ) t1 
            on SUBSTRING(t.column2,t1.N,1) = '.'
         ) t1
          on t1.column2=t.column2
)a
pivot
( 
    max(split_values) for r in ([1],[2],[3],[4])
   )p
  order by [1] desc,[2] desc,[3] desc,[4] desc

Castellano