Hello,
I'm trying to sort a training table in order of the sequence of tasks. These tasks are numbered
1.1.1
1.1.2
1.1.3
......
1.1.10
1.1.11
When the query sorts this and displays it, it comes up:
1.1.1
1.1.10
1.1.11
1.1.2
....
Is there a way to get them to come up in order similar to the way I first displayed the numbers?
In the table, they may not end up in order, so I can't order it by any other identifier.
Thanks!
Hi,I dont know what kind of Database you are using here.
But i have a solution for you if it is in Mssql
But you do not need to wory,a little change may be needed incase Database is not Mssql
this is the code
select * from tableName order by convert (int,replace (fieldName,'.','') )1 ) tableName is the name of your table
2 ) fieldName is the field on which you want a particular order by clause
3 ) The rest things are simple to understand,it sorts on your fieldname,but before it gets only the data that is without '.'
So a value 1.1.15
gets converted into 1115
Then i convert it into int
So then our sort can easily work on int.
Hope it helps you. :)
I am using MSSQL.
I have taken your advice. It does order everything differently, but...
Here is what the data is in the table, in the desired order:
1.1.1
1.1.2
1.1.10
1.1.11
1.2.1
1.2.2
1.3.1
Here is the result after the suggested code:
1.1.1
1.1.2
1.2.1
1.2.2
1.3.1
1.1.10
1.1.11
I understand what the computer is doing, I just don't know how to get the results the way I want.
Your problem is coming from the field being treated as a string rather than a number. Basically you have two options.
1. Split the files into tree separate fields as integers and then do the order by using all three fields.
2. Change the columns that need it in your data to be 2 digit based instead of one or two.
So the data would look like this possibly:
1.1.01
1.1.02
1.1.03
......
1.1.10
1.1.11
@rch1231 I see what you're saying, and I thought about actually doing that. But, as most programmers do, I am building this software for another department in my building. So changing simple things like this only prompts more questions and fear from non-computer-savy people. Unfortunately, more than 90% of the users of this program are people that do not understand Google (i'm not trying to be rude, just being honest).
I may have to go ahead and change everything, but I'm searching for a solution without changing what I have already.
Okay, I'm going to put in my 2 cents worth. Some assumptions on my part:
1. You don't necessarily have all three nodes; i.e. you could have 1.1, 1.1.1, 1.1.2, 1.2, 1.2.1, etc.
2. You never have more than three nodes; i.e. you never have "1.1.1.1"
3. All the nodes are numeric; i.e. you never have "1.1.1a".
If all the above are true, this should work. It's ugly, but it does work.
select
myTestCol,
charindex('.', myTestCol, 1) as firstPeriod,
charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) as secondPeriod,
case
when charindex('.', myTestCol, 1) <> 0
then substring(myTestCol, 1, charindex('.', myTestCol, 1) - 1)
else myTestCol
end as node1,
case
when charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) <> 0
then substring(myTestCol, charindex('.', myTestCol, 1) + 1, charindex('.', myTestCol, charindex('.', myTestCol, 1)) - charindex('.', myTestCol, 1) + 1 )
when charindex('.', myTestCol, 1) <> 0
then substring(myTestCol, charindex('.', myTestCol, 1) + 1, len(myTestCol) - charindex('.', myTestCol, 1))
else 0
end as node2,
case
when charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) <> 0
then substring(myTestCol, charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) + 1, len(myTestCol) - charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1))
else 0
end as node3
from dbo.mytest2
order by node1, node2, node3Brief explanation:
charindex is a function that looks for the offset of one string inside another string. Using that, you can isolate the nodes. Once that happens, you can sort on them individually. Since they are all numeric, they are treated as integers rather than character. This has been tested on SQL2000, SQL2005 and SQL2008. Hope this helps!
Almost forgot...Here's the table definition and the test data insert. Try running the above query using "order by mytestcol" rather than node1, node2, node3 to prove it actually works.
create table dbo.MyTest2
( myTestCol varchar(25)
)
go
insert into dbo.myTest2 (myTestCol) values ('1')
insert into dbo.myTest2 (myTestCol) values ('1.1')
insert into dbo.myTest2 (myTestCol) values ('1.1.1')
insert into dbo.myTest2 (myTestCol) values ('1.1.2')
insert into dbo.myTest2 (myTestCol) values ('1.1.3')
insert into dbo.myTest2 (myTestCol) values ('1.1.4')
insert into dbo.myTest2 (myTestCol) values ('1.1.5')
insert into dbo.myTest2 (myTestCol) values ('1.1.6')
insert into dbo.myTest2 (myTestCol) values ('1.1.7')
insert into dbo.myTest2 (myTestCol) values ('1.1.8')
insert into dbo.myTest2 (myTestCol) values ('1.1.9')
insert into dbo.myTest2 (myTestCol) values ('1.1.10')
insert into dbo.myTest2 (myTestCol) values ('1.1.11')
insert into dbo.myTest2 (myTestCol) values ('1.2')
insert into dbo.myTest2 (myTestCol) values ('1.2.1')
insert into dbo.myTest2 (myTestCol) values ('1.2.2')
insert into dbo.myTest2 (myTestCol) values ('1.2.3')
insert into dbo.myTest2 (myTestCol) values ('1.2.4')
insert into dbo.myTest2 (myTestCol) values ('1.2.5')
insert into dbo.myTest2 (myTestCol) values ('1.2.6')
insert into dbo.myTest2 (myTestCol) values ('1.2.7')
insert into dbo.myTest2 (myTestCol) values ('1.2.8')
insert into dbo.myTest2 (myTestCol) values ('1.2.9')
insert into dbo.myTest2 (myTestCol) values ('1.2.10')
insert into dbo.myTest2 (myTestCol) values ('1.2.11')
insert into dbo.myTest2 (myTestCol) values ('1.3')
insert into dbo.myTest2 (myTestCol) values ('1.3.1')
insert into dbo.myTest2 (myTestCol) values ('1.3.2')
insert into dbo.myTest2 (myTestCol) values ('1.3.3')
insert into dbo.myTest2 (myTestCol) values ('1.3.4')
insert into dbo.myTest2 (myTestCol) values ('2.3.2')
insert into dbo.myTest2 (myTestCol) values ('2.3.3')
insert into dbo.myTest2 (myTestCol) values ('2.3.4')
insert into dbo.myTest2 (myTestCol) values ('2.3.5')
insert into dbo.myTest2 (myTestCol) values ('2.3.6')
insert into dbo.myTest2 (myTestCol) values ('2.3.7')
insert into dbo.myTest2 (myTestCol) values ('2.3.8')
insert into dbo.myTest2 (myTestCol) values ('2.3.9')
insert into dbo.myTest2 (myTestCol) values ('2.3.10')
insert into dbo.myTest2 (myTestCol) values ('2.3.11')
insert into dbo.myTest2 (myTestCol) values ('1.3.5')
insert into dbo.myTest2 (myTestCol) values ('1.3.6')
insert into dbo.myTest2 (myTestCol) values ('1.3.7')
insert into dbo.myTest2 (myTestCol) values ('1.3.8')
insert into dbo.myTest2 (myTestCol) values ('1.3.9')
insert into dbo.myTest2 (myTestCol) values ('1.3.10')
insert into dbo.myTest2 (myTestCol) values ('1.3.11')
insert into dbo.myTest2 (myTestCol) values ('2')
insert into dbo.myTest2 (myTestCol) values ('2.1')
insert into dbo.myTest2 (myTestCol) values ('2.1.1')
insert into dbo.myTest2 (myTestCol) values ('2.1.2')
insert into dbo.myTest2 (myTestCol) values ('2.1.3')
insert into dbo.myTest2 (myTestCol) values ('2.1.4')
insert into dbo.myTest2 (myTestCol) values ('2.1.5')
insert into dbo.myTest2 (myTestCol) values ('2.1.6')
insert into dbo.myTest2 (myTestCol) values ('2.1.7')
insert into dbo.myTest2 (myTestCol) values ('2.1.8')
insert into dbo.myTest2 (myTestCol) values ('2.1.9')
insert into dbo.myTest2 (myTestCol) values ('2.1.10')
insert into dbo.myTest2 (myTestCol) values ('2.1.11')
insert into dbo.myTest2 (myTestCol) values ('2.2')
insert into dbo.myTest2 (myTestCol) values ('2.2.1')
insert into dbo.myTest2 (myTestCol) values ('2.2.2')
insert into dbo.myTest2 (myTestCol) values ('2.2.3')
insert into dbo.myTest2 (myTestCol) values ('2.2.4')
insert into dbo.myTest2 (myTestCol) values ('2.2.5')
insert into dbo.myTest2 (myTestCol) values ('2.2.6')
insert into dbo.myTest2 (myTestCol) values ('2.2.7')
insert into dbo.myTest2 (myTestCol) values ('2.2.8')
insert into dbo.myTest2 (myTestCol) values ('2.2.9')
insert into dbo.myTest2 (myTestCol) values ('2.2.10')
insert into dbo.myTest2 (myTestCol) values ('2.2.11')
insert into dbo.myTest2 (myTestCol) values ('2.3')
insert into dbo.myTest2 (myTestCol) values ('2.3.1')Hey,try this
SELECT * FROM tableName ORDER BY convert (varchar(20),REPLACE (fieldName,'.','') )Akash, your solution won't work for the same reason as your prior one...still sorts
1.1.1
1.1.2
1.1.3
...
1.1.10
as
111
1110
112
113
114
etc.
Be sure to test before you post!
Hi BitBit,please try my solution on your sample created table
I think it is giving the right out put as per my query.
so please try once.
SELECT * FROM mytest2 ORDER BY convert (varchar(20),REPLACE (mytestcol,'.','') ) descResults:
myTestCol
-------------------------
2.3.9
2.3.8
2.3.7
2.3.6
2.3.5
2.3.4
2.3.3
2.3.2
2.3.11
2.3.10
2.3.1
2.3
2.2.9
2.2.8
2.2.7
2.2.6
2.2.5
2.2.4
2.2.3
2.2.2
2.2.11
2.2.10
2.2.1
2.2
2.1.9
2.1.8
2.1.7
2.1.6
2.1.5
2.1.4
2.1.3
2.1.2
2.1.11
2.1.10
2.1.1
2.1
2
1.3.9
1.3.8
1.3.7
1.3.6
1.3.5
1.3.4
1.3.3
1.3.2
1.3.11
1.3.10
1.3.1
1.3
1.2.9
1.2.8
1.2.7
1.2.6
1.2.5
1.2.4
1.2.3
1.2.2
1.2.11
1.2.10
1.2.1
1.2
1.1.9
1.1.8
1.1.7
1.1.6
1.1.5
1.1.4
1.1.3
1.1.2
1.1.11
1.1.10
1.1.1
1.1
1
So it's working. Remove Desc from the end,then it will give you the Ascending version of the output he wanted.
My dear Akash, I respectfully ask you to look at the original question, then at the results again. Your solution doesn't match the spec.
Think of sections of a training manual. Chapter 1, section 1 page 1 is represented as 1.1.1. Chapter 1, section 1 page 2 is represented 1.1.2. Chapter 1 section 1 page 11 is 1.1.11, and so on.
Sorry,if i was wrong here. Perhaps i may be wrong.
And even the thread starter is not giving any feedback that whether he has got the solution or not.
Don't mind BitBit :)
Sorry for the absence guys! My job requires much more than programming, and so I have spent a few days busy with some other stuff.
@akash - bitbit is correct.
@bitbit - Some of your assumptions are correct, but not all. My response is in bold.
Okay, I'm going to put in my 2 cents worth. Some assumptions on my part:
1. You don't necessarily have all three nodes; i.e. you could have 1.1, 1.1.1, 1.1.2, 1.2, 1.2.1, etc.correct. In fact, some may only be one number, with no periods. Sometimes, one table will have multiple formats of these numbers (i.e.: 1.1, 1.1.1, 1.2, 1.2.1, etc...).
2. You never have more than three nodes; i.e. you never have "1.1.1.1"Sorry - not true. On occasion, there will be a fourth digit. I think I understand your code enough to compensate - we'll find out!
3. All the nodes are numeric; i.e. you never have "1.1.1a".The tables should not have any letters in them, but there also are not any fool-proofs to make sure that letters do not get entered.
I hope this answers any questions thus far. I will try bitblt's code today, and post my results. Again, sorry for the delay in response. And thank you for your input.
@bitblt - My results only produce an error message. I have gotten different versions of the same message as I change things to try and fix it, but here is the most recent one:
Msg 245, Level 16, State 1, Procedure ProcedureName, Line 7
Syntax error converting the nvarchar value '3.0' to a column of data type int.
Any ideas?
I meant to put this in my last post, but forgot....
I added a few lines that search for a specific set of lines. I would guess that this has nothing to do with the problem, but just to make sure you have all of the information.
The lines that I am selecting, I have chosen specifically because the list goes: 1, 2, 3, 3.0, 3.1, 4, 5, 6, 6.0 (there are more, but we have not made it this far).
Your help is much appreciated.
Not enough information for me to go on...can you post the SQL statement you have already done?
Not much has changed from what you gave me. I tried giving it the code to understand a fourth number (node4), but had problems with it so I took it out.
I'm using M$ SQL Server Management Studio 2008 to do this. I plan to call the procedure from a program that I am building in M$ Visual Studio VB .net 2008. (not sure if that information is relevant, but there it is)
@docID nvarchar(50)
AS
BEGIN
-- Insert statements for procedure here
SELECT itemNo, CHARINDEX('.', itemNo, 1) AS firstPeriod,
CHARINDEX('.', itemNo, CHARINDEX('.', itemNo, 1) + 1) AS secondPeriod,
CASE WHEN charindex('.', itemNo, 1) <> 0
THEN substring(itemNo, 1, charindex('.', itemNo, 1) - 1)
ELSE itemNo
END AS node1,
CASE WHEN charindex('.', itemNo, charindex('.', itemNo, 1) + 1) <> 0
THEN substring(itemNo, charindex('.', itemNo, 1) + 1, charindex('.', itemNo, charindex('.', itemNo, 1)) - charindex('.', itemNo, 1) + 1)
WHEN charindex('.', itemNo, 1) <> 0
THEN substring(itemNo, charindex('.', itemNo, 1) + 1, len(itemNo) - charindex('.', itemNo, 1))
ELSE 0
END AS node2,
CASE WHEN charindex('.', itemNo, charindex('.', itemNo, charindex('.', itemNo, 1) + 1) + 1) <> 0
THEN substring(itemNo, charindex('.', itemNo, charindex('.', itemNo, 1) + 1) + 1, len(itemNo) - charindex('.', itemNo, charindex('.', itemNo, 1) + 1))
ELSE 0
END AS node3
FROM docContent
WHERE docID = @docID
ORDER BY node1, node2, node3
ENDOkay, let me just say that this bad boy does NOT scale well. And, you'd probably be better off in the long run just biting the bullet and re-casting your data structures so you don't have to do that. Disclaimer aside, this was a fun challenge, and here's what I came up with:
select myTestCol,
charindex('.', myTestCol, 1) as firstPeriod,
charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) as secondPeriod,
case
when charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) > 0
then charindex('.', myTestCol, charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) + 1)
else 0
end as thirdPeriod,
case when charindex('.', myTestCol, charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) + 1) > 0
then charindex('.', myTestCol, charindex('.', myTestCol, charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) + 1) + 1)
else 0
end as fourthPeriod,
case when charindex('.', myTestCol, charindex('.', myTestCol, charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) + 1) + 1) > 0
and charindex('.', myTestCol, charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) + 1) > 0
and charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) > 0
then charindex('.', myTestCol, charindex('.', myTestCol, charindex('.', myTestCol, charindex('.', myTestCol, charindex('.', myTestCol, 1) + 1) + 1) + 1) + 1)
else 0
end as fifthPeriod
into #temp1
from dbo.mytest2
select
myTestCol,
firstPeriod,
case when firstPeriod <> 0
then substring(myTestCol, 1, firstPeriod - 1)
else myTestCol
end as node1,
secondPeriod,
case
when secondPeriod <> 0
then substring(myTestCol, firstPeriod + 1, secondPeriod - firstPeriod - 1)
when firstPeriod <> 0
then substring(myTestCol, firstPeriod + 1, len(myTestCol) - firstPeriod)
else 0
end as node2,
thirdPeriod,
case
when thirdPeriod <> 0
then substring(myTestCol, secondPeriod + 1, thirdPeriod - secondPeriod - 1)
when secondPeriod <> 0
then substring(myTestCol, secondPeriod + 1, len(myTestCol) - secondPeriod)
else 0
end as node3,
fourthPeriod,
case
when fourthPeriod <> 0
then substring(myTestCol, thirdPeriod + 1, fourthPeriod - thirdPeriod - 1)
when thirdPeriod <> 0
then substring(myTestCol, thirdPeriod + 1, len(myTestCol) - thirdPeriod)
else 0
end as node4,
fifthPeriod,
case
when fifthPeriod <> 0
then substring(myTestCol, fourthPeriod + 1, fifthPeriod - fourthPeriod - 1)
when fourthPeriod <> 0
then substring(myTestCol, fourthPeriod + 1, len(myTestCol) - fourthPeriod)
else 0
end as node5
from #temp1
order by node1, node2, node3, node4
drop table #temp1Now please notice that in order to hide the complexity somewhat, I used a temp table to hold interim results so I wouldn't have to replicate those horrendous case statements to figure out where the "." were in the data. If you need to use it as a single statement rather than put it in a stored proc, you can just CAREFULLY replace each instance of (for instance) "fourthPeriod" in the second SQL statement with the ugly "case" statement corresponding to it from the top. Personally I don't recommend that, but sometimes you don't have a choice. Good luck!
Sometimes, ugly things can produce beautiful results. I analyzed the code, and I will admit that I am not 100% sure of everything, but I understand enough to get the idea. I attached a picture of the results in case you're interested (i couldn't paste a screen shot in the post).
Your help with this is MUCH appreciated. I hope you have a wonderful day!
btw - i like your icon!
My pleasure,and you can mail me the result image by getting my id from here as I am interested in the output.
And it is not an icon,its me :)
Happy Coding :)
@bklynman, you're welcome. It was a fun challenge.
And the icon is the robot character GIR from the Invader Zim series. He's wearing his dog suit and riding his pig. If that makes no sense, here's a link for your perusal:
http://en.wikipedia.org/wiki/List_of_Invader_Zim_characters
Perhaps it will also give you a little insight on my personality. ;)
I am definitely fully aware of Invader Zim. That cartoon was awesome, and I am sad that it was canceled. My Zim DVDs sit right next to my Ren & Stimpy DVD. :)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.