How to order query result by multipart X.Y[.Z] "version" numbers?

Question

I have this data:

nov_id
2.1.1 
2.1.10
2.1.11
2.1.12
2.1.13
2.1.14
2.1.2 
2.1.3 
2.1.4 
2.1.5 
2.1.6 
2.1.7 
2.1.8 
2.1.9 
2.2   
2.3   
2.4   
2.5   
2.6

I need to order my results so my result expected is this:

nov_id
2.1.1 
2.1.2 
2.1.3 
2.1.4 
2.1.5 
2.1.6 
2.1.7 
2.1.8 
2.1.9 
2.1.10
2.1.11
2.1.12
2.1.13
2.1.14
2.2   
2.3   
2.4   
2.5   
2.6

This is one of my tries:

Select nov_id 
From dbo.NS_tbl_sc_novedad
Order by Convert(int,Left(Ltrim(Rtrim(replace(nov_id,'.','')))+'0000',4));

I tried to paste some zero's and order by that but, obviously I don't get it yet.

Your value 2.1.1 is not numeric and hence cannot be converted to float in order to sort the column. — Paresh J, Feb 05 '15 at 15:47

Gordon Linoff · Answer 1 · 2015-02-05T16:04:55.150

2

For your particular data, this will work:

order by left(mov_id, 3),
         len(mov_id),
         mov_id

The idea is to order by the length, because the smaller numbers at the end have a shorter length -- given how the values are stored.

This can be revised to be more general, depending on what your data really looks like.

edited Feb 05 '15 at 16:04

answered Feb 05 '15 at 15:46

Gordon Linoff

1,242,037
58
646
786

2

It also won't work when the first part goes to double digits (ex 10.1.1) – Sean Lange Feb 05 '15 at 15:50
1

This explicitly does work on the sample data. This is not advertised to work on every possible combination of data. – Gordon Linoff Feb 05 '15 at 16:04

score 2 · Answer 2 · edited May 23 '17 at 10:26

2

How about:

SELECT nov_id
FROM @example
ORDER BY 
  CASE WHEN PARSENAME(nov_id, 3) IS NULL THEN 
    CAST(PARSENAME(nov_id, 2) AS INTEGER)*10000
    +CAST(PARSENAME(nov_id, 1) AS INTEGER)*100
  ELSE 
    CAST(PARSENAME(nov_id, 3) AS INTEGER)*10000
    +CAST(PARSENAME(nov_id, 2) AS INTEGER)*100
    +CAST(PARSENAME(nov_id, 1) AS INTEGER)
  END

Borrowing from Nathan Bedford's 'hijacking' the PARSENAME function in How do I split a string so I can access item x?

It'll work for numbers up to 99.99.99. To support more digits you'd need to increase the multiples in the CASE statement.

edited May 23 '17 at 10:26

Community

1
1

answered Feb 05 '15 at 16:06

Elliveny

2,159
1
20
28

Please try to increase to 4 dots :) something like 1.20.3.2.1 – Giorgi Nakeuri Feb 05 '15 at 16:18
This technique won't do that! – Elliveny Feb 05 '15 at 16:20

score 1 · Accepted Answer · answered Feb 05 '15 at 16:01

This should work with any string that has 2 or 3 parts with any number of digits in the number, e.g 1546.345.245 and 999.34

select
  nov_id
from data
cross apply (
  select charindex('.', nov_id) as pos
) as c1
cross apply (
   select charindex('.', nov_id, c1.pos+1) as pos
) as c2
order by
  convert(int, left(nov_id, c1.pos-1)),
  convert(int, substring(nov_id, c1.pos+1, isnull(nullif(c2.pos, 0), 100)-c1.pos-1)),
  convert(int, case c2.pos when 0 then 0 else substring(nov_id, c2.pos+1, 100) end)

Looks little messy, though :)

ricky89 · Answer 4 · 2015-02-05T15:59:58.917

This is my (possibly over engineered solution)...but it works

    declare @temp table (value varchar(20),orderby int)

insert into @temp (value)
Values( '2.1.1'),
('2.1.10'),
('2.1.11'),
('2.1.12'),
('2.1.13'),
('2.1.14'),
('2.1.2'),
('2.1.3'), 
('2.1.4'),
('2.1.5'),
('2.1.6'),
('2.1.7'),
('2.1.8'),
('2.1.9'),
('2.2'),   
('2.3'),   
('2.4'),   
('2.5'),   
('2.6') 

UPdate @temp set orderby= case when len(replace(value,'.',''))=2 then replace(value,'.','')*100
                               when len(replace(value,'.',''))=3 then replace(value,'.','')*10
                               else replace(value,'.','') end
SELECT value FROM @temp
order by orderby ASC

The above gives the result set

2.1.1
2.1.10
2.1.11
2.1.12
2.1.13
2.1.14
2.1.2
2.1.3
2.1.4
2.1.5
2.1.6
2.1.7
2.1.8
2.1.9
2.2
2.3
2.4
2.5
2.6

How to order query result by multipart X.Y[.Z] "version" numbers?

4 Answers4