Problem with Sequence Number
-
You can have your cake and eat it too. Here is a solution that does not need a function for sorting your strings. I don't know if there is a speed difference on small sets, comparing to Ryan's solution, but on large sets this method is 3400 times as fast on my computer compared to the function method.
------------------------------------------------------------------------------
DECLARE @test-2 table (row tinyint identity(0, 1), ver varchar(30), k bigint, z tinyint)
insert @test-2 (ver)
select '1' union
select '1.0' union
select '1.1' union
select '1.2.1' union
select '1.2.1.2.1' union
select'1.2' union
select '10.1' union
select '2.1' union
select '1.10'
declare @parts tinyint, @digits tinyint
select @parts = 9, -- max number of digit parts x.y.z.t.a.b.c
@digits = 2 -- number of digits for each part 99.99.99 or 999.999
-- note that @parts * @digits must be 18 or less for bigint
declare @temp table (row tinyint, z tinyint, p smallint)
insert @temp
SELECT row,
len(replace(substring(ver, 1, i), '.', '')),
convert(smallint, SUBSTRING('.' + ver + '.', i + 1, CHARINDEX('.', '.' + ver + '.', i + 1) - (i + 1)))
FROM @test, (select 1 i union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) w
WHERE i = CHARINDEX('.', '.' + ver + '.', i) AND i < len('.' + ver)
ORDER BY row, i
update @test-2
set k = g.y,
z = g.f
from @test-2 t, (select row, max(z) f, sum(p * power(power(10.0, @digits), @parts - z)) y from @temp group by row) g
where t.row = g.row
select ver from @test-2 order by k, z
N 56°04'39.16"
E 12°55'05.25" -
Well, I was enjoying the kind feedback, and then I came across your idea, Peter. Very nice indeed!
I think I've been outdone here, but to minimise the blow, I've 'enhanced' your idea a little
All I've done really is stripped out the unnecessary stuff like the row number and the temporary table to make it more 'elegant' - but probably even faster too. Maybe the next step would be to reduce the '18' limitation by using logs or something. That's for another time, though, I feel...
--data
declare @t table (v varchar(30))
insert into @t
select '1'
union all select '1.0'
union all select '1.1'
union all select '1.2.1'
union all select '1.2.1.2.1'
union all select '1.2'
union all select '10.1'
union all select '2.1'
union all select '1.10'
--calculation - create a large integer based on the version number, and order by that.
declare @numbers table (i int identity(0, 1), x bit)
insert @numbers select top 30 null from master.dbo.syscolumns a
declare @maxparts tinyint, @maxdigits tinyint
set @maxparts = 9 -- max number of digit parts x.y.z.t.a.b.c
set @maxdigits = 2 -- max number of digits for each part 99.99.99 or 999.999
select v
from @t, @numbers
where substring('.' + v, i, 1) = '.'
group by v
order by sum(substring(v, i, charindex('.', v + '.', i)-i) *
power(power(10.0, @maxdigits), @maxparts - i
+ len(replace(left(v, i), '.', ''))))
--results
v
------------------------------
1
1.0
1.1
1.2
1.2.1
1.2.1.2.1
1.10
2.1
10.1
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I just couldn't let this one slip away
Perhaps Ryan can take a look at this approach, even it is not completely setbased. it's late now, 00:32 am
declare @t table (v varchar(30))
insert @t
select '1'
union all select '1.0'
union all select '1.1'
union all select '1.2.1'
union all select '1.2.1.2.1'
union all select '1.2'
union all select '10.1'
union all select '2.1'
union all select '1.10'
declare @numbers table (i int identity(0, 1), x bit)
insert @numbers select top 60 null from master.dbo.syscolumns a, master.dbo.syscolumns b
declare @d table (n int identity(0, 1), v varchar(30), z int)
insert @d
select v, charindex('.', v, i)
from @t, @numbers
where substring( v , i, 1) = '.'
union select v, len(v) + 1 from @t
union select v, 0 from @t
order by 1, 2
declare @maxdigits int
select @maxdigits = 10 --automatic maxparts is now 8000/@maxdigits. for @maxdigits = 10, maxparts is 800!!
declare @out table (n int identity(0, 1), v varchar(30), p varchar(30))
insert @out (v, p)
select d1.v, right(replicate('0', @maxdigits) + substring(d1.v, d1.z + 1, d2.z - d1.z - 1), @maxdigits)
from @d d1
left join @d d2 on d1.n = d2.n - 1
where d2.z > d1.z
order by d1.n
DECLARE @List varchar(8000)
SELECT @list = COALESCE(@list + p, p)
from @out
select v from @out group by v
order by substring(@list, 1 + @maxdigits * min(n), @maxdigits * count(*) )
N 56°04'39.16"
E 12°55'05.25" -
Peter - interesting approach
Maybe I'm misunderstanding, but it looks like that might run into problems when there are lots of rows (because there's only one string to hold all values for all rows).
How about this - based around the same idea (but having one string for each version number)...
--data
declare @t table (v varchar(100))
insert into @t
select '1'
union all select '1.0'
union all select '1.1'
union all select '1.2.1'
union all select '1.2.1.2.1'
union all select '1.2'
union all select '10.1'
union all select '2.1'
union all select '1.10'
union all select '1.2.1.2.1.32179.31280912731278.211212.1.2.3.4.5.6.7'
--more data
declare @i int
set @i = 3
while @i <= 10
begin
insert @t select v + '.' + cast(@i as varchar(2)) from @t
set @i = @i + 1
end
--calculation - create a concatenated string based on the version number, and order by that.
declare @numbers table (i int identity(0, 1), x bit)
insert @numbers select top 100 null from master.dbo.syscolumns a
declare @d table (i int identity(1, 1) primary key, v varchar(100), p varchar(30), y varchar(7900))
insert @d (v, p)
select v, substring(v, i, charindex('.', v + '.', i+1)-i)
from @t, @numbers
where substring('.' + v, i, 1) = '.'
order by 1, i
declare @y varchar(7900)
declare @previousv varchar(100)
--potential issue: sql server doesn't guarantee that these will be ordered correctly in the update
update @d set @y = case when @previousv = v then isnull(@y, '') else '' end
+ replicate('0', 30 - len(p)) + p, y = @y, @previousv = v
select v from @d d where i = (select max(i) from @d where v = d.v) order by y
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply