Split a string and return greatest in mssql

Question

I need to find a way to get the data with the highest versionNumber.

Here is my database design:

VERSIONNUMBER - varchar(15)
DOWNLOADPATH - varchar(100)

Lets say I have records like:

VERSIONNUMBER -------- DOWNLOADPATH
1.1.2                  a.com
1.1.3                  b.com
2.1.4                  c.com
2.1.5                  d.com
2.2.1                  e.com

I need to get the record with the versionnumber 2.2.1. Need some help with the sql though :)

Thank you for any help

Answer 1

Alternatively, instead of multiplying each digit group, you can use ranking:

with a as
(
    select * from (values
        ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') 
    ) as b(c)     
),
x as
(
select c, 
    Ranking = RANK() over(order by convert(int,PARSENAME(c,3)), convert(int,PARSENAME(c,2)), convert(int,PARSENAME(c,1))) 
from a
)
select * from x 
    order by ranking

Yields:

c   Ranking
2.1.4   1
2.1.5   2
2.1.12  3
2.2.1   4

Final query:

with a as
(
    select * from (values
        ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') 
    ) as b(c)     
),
x as
(
select c, 
    Ranking = RANK() over(order by convert(int,PARSENAME(c,3)), convert(int,PARSENAME(c,2)), convert(int,PARSENAME(c,1))) 
from a
)
select * 
from x  
where Ranking = (select MAX(ranking) from x)

Output:

c   Ranking
2.2.1   4

Another simple approach, sort descending then just get the first row:

with a as
(
    select * from (values
        ('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)     
),
x as
(
select c, 
    Ranking = RANK() 
        over(order by 
            convert(int,PARSENAME(c,3)) desc, 
            convert(int,PARSENAME(c,2)) desc, 
            convert(int,PARSENAME(c,1)) desc) 
from a
)
select * 
from x  
where Ranking = 1

Answer 2

select top 1 DOWNLOADPATH
from YourTable
order by cast(parsename(VERSIONNUMBER, 3) as int) desc,
         cast(parsename(VERSIONNUMBER, 2) as int) desc,
         cast(parsename(VERSIONNUMBER, 1) as int) desc

Answer 3

If you are on SQL Server 2008 you can lever the HIERARCHYID datatype.

SELECT VersionNumber, DownloadPath
FROM (VALUES
    ('1.1.2','a.com'),
    ('1.1.3','b.com'),
    ('2.1.4','c.com'),
    ('2.1.5','d.com'),
    ('2.2.1','e.com')        
     ) AS T(VersionNumber, DownloadPath)
ORDER  BY CAST('/' + VersionNumber + '/' AS HIERARCHYID) DESC