Problem with Sequence Number
-
I am writing a program that store the following information in a table.
Item e.g 1.1, 1.2, 1.2.1, 2.1, 10.1, etc - currently varchar(50)
Description e.g. Supply cameras etc.
My problem is this, when you run a SQL query and order by Item asc. it will bring back the sequence numbers as follows.
1.1, 10.1, 1.2, 1.2.1, 2.1
and it need to be
1.1, 1.2, 1.2.1, 2.1, 10.1
Has anyone ever come accross a similar problem and what was the solution?
-
Unless you are going to have unlimited sub-sub-sub-...categories in your item numbers, you would ideally decompose this data into cat, subcat, subsubcat. you could then order properly, as well as generally exposing more information to the consumers of your data.
I'll have a look at doing some code to extract those values assuming a max of three elements, and assuming that the first element is the top-level category etc. You could use this either in an order by or in a migration script when you change the data model.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
i did something similar as well;in mine i assumed 4 sets like an ip address( it was actually version information, major/minor/revision andthe fourth param i can't remember right now) , i've got it as a function, so i created a calculated column in the table that used the function so i could use an order by that made sense;
i'll look for the script when i get to work. and update it here.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Another possibility would be to have a fixed number of characters in each part, which would mean adding leading zeros to your current data. If you know that the numbers will never be greater than 99, it would look like that:
1.1 -> 01.01
10.1 -> 10.01
27.2.3.14.5 -> 27.02.03.14.05
This should make ordering work as required. I'm not sure whether you can do that (change the data), but I had similar issue and solved it this way.
BTW, in your example SQL would order like this : 1.1, 1.2, 1.2.1, 10.1, 2.1 - but I suppose 10.1 on second place is just a typo.
-
Thanks for all the ideas, will have to look at the best way forward.
-
Can you store the number as a DECIMAL instead of a VARCHAR?
VARCHAR, being a string, sorts alphabetically.
DECIMAL, being a number, will sort numbers propery.
-SQLBill
-
I don't think DECIMAL is an option because of multiple dots in the values : 1.2.1 can not be converted to DECIMAL or any other numeric format.
-
ok here is the function and an example of how i use it; like i said, i use a calculated column for the sort order ;
also note that there is no validation if someone enters firstname.lastname instead of all numeric values 1.2.3;
you end up getting errors like "Syntax error converting the varchar value 'firstname' to a column of data type int."
maybe someone else can suggest some validations for the function.
the function is just a modification of one of the SPLIT functions found in the SSC contributions
--select dbo.fn_sortval('145.2.3.3.2')
--select dbo.fn_sortval('1.2')
--select dbo.fn_sortval('1.2.1.3.2')
--select dbo.fn_sortval('145.2.3.3.2')
--no error checking if the parameters are not numeric characters.
CREATE function fn_sortval(
@vcDelimitedString varchar(8000)
)
returns int
as
begin
declare
@vcDelimiter varchar(100),
@siIndex smallint,
@siStart smallint,
@siDelSize smallint,
@SortVal int,
@i int
set @vcDelimiter='.' --hardcoded for a period, change if your list was dash delimited or something
set @SortVal=0
set @i=1000 --assuming 10^4 for 4 levels of hierarchy -4 fields like an IP: 11.22.33.44
--important because with this 10.2 is less than 10.2.1
SET @siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
--print @vcDelimitedString
set @SortVal=@SortVal + convert(int,@vcDelimitedString) * @i
--INSERT INTO @tblArray VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
--print SUBSTRING(@vcDelimitedString, 1,@siIndex - 1)
set @SortVal=@SortVal + convert(int,SUBSTRING(@vcDelimitedString, 1,@siIndex - 1)) * @i
--INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
set @i=@i / 10
END
return @SortVal
end
GO
create table test(tstval varchar(30),sortorder as dbo.fn_sortval(tstval))
insert into test
select '1.1' union
select '1.2' union
select'1.2.1' union
select '2.1' union
select '10.1'
select * from test order by sortorder
results:
1.1 1100 1.2 1200 1.2.1 1210 2.1 2100 10.1 10100 Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here is another solution
DECLARE @test-2 table
(
tstval varchar(30),
sortorder as case charindex('.', tstval)
when 0 then tstval
else substring(tstval,1,charindex('.', tstval)-1) + '.'+ REPLACE(substring(tstval,charindex('.', tstval) + 1, LEN(tstval)),'.','')
end
)
insert into @test-2
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'
select * from @test-2
order by cast(sortorder as decimal)
-
Nagabhushanam Ponnapalli - try adding '1.10' to your data
Here's a (relatively) simple idea/solution with the limitation that there must be no more than 99 children - although I guess it could be extended without much effort.
--function
create function dbo.fnVersionSortValue(@v varchar(30)) returns varchar(30) as
begin
declare @i int
while 0 = 0
begin
set @i = patindex('%.[0-9].%', '.' + @v-2 + '.')
if @i = 0 break
set @v-2 = stuff(@v, @i, 0, '0')
end
return @v-2
end
go
--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
select * from @t order by dbo.fnVersionSortValue(v)
--results
v
-----------
1
1.0
1.1
1.2
1.2.1
1.2.1.2.1
1.10.10.1
2.1
10.1
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Nice...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This topic is another proof that all complexities with queries raise mostly from wrong database design.
_____________
Code for TallyGenerator -
Absolutely concur... vehemently... heck, a third of the questions on these forums are because of bad database design.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi, thanks for all the solutions, I am using the function from RyanRandall in my code and it works great.
Thanks very much!!!
-
Ryan is like that... writing good stuff, I mean.
Thanks for the feedback, Janno.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply