desired results from ORDER BY

Question

bklynman01 2 Junior Poster in Training

13 Years Ago

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!

mssql

4 Contributors
23 Replies
119 Views
1 Week Discussion Span
Latest Post 13 Years Ago Latest Post by bklynman01

All 23 Replies

Akash Saikia 9 Junior Poster

13 Years Ago

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. :)

rch1231 169 Posting Shark

13 Years Ago

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

BitBlt 452 Practically a Master Poster

13 Years Ago

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, node3

Brief 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!

BitBlt 452 Practically a Master Poster

13 Years Ago

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')

Akash Saikia 9 Junior Poster

13 Years Ago

Hey,try this

SELECT * FROM tableName ORDER BY convert (varchar(20),REPLACE (fieldName,'.','') )

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

bklynman01 2 Junior Poster in Training · Answer 1 · 2011-04-04T20:03:28+00:00

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.

bklynman01 2 Junior Poster in Training · Answer 2 · 2011-04-04T21:02:36+00:00

@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.

BitBlt 452 Practically a Master Poster Featured Poster · Answer 3 · 2011-04-07T04:17:18+00:00

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!

Akash Saikia 9 Junior Poster · Answer 4 · 2011-04-07T10:29:55+00:00

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,'.','') ) desc

BitBlt 452 Practically a Master Poster Featured Poster · Answer 5 · 2011-04-07T20:21:14+00:00

Results:
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

Akash Saikia 9 Junior Poster · Answer 6 · 2011-04-08T10:06:24+00:00

So it's working. Remove Desc from the end,then it will give you the Ascending version of the output he wanted.

BitBlt 452 Practically a Master Poster Featured Poster · Answer 7 · 2011-04-08T12:46:47+00:00

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.

Akash Saikia 9 Junior Poster · Answer 8 · 2011-04-08T12:53:44+00:00

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 :)

bklynman01 2 Junior Poster in Training · Answer 9 · 2011-04-12T00:03:09+00:00

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.

bklynman01 2 Junior Poster in Training · Answer 10 · 2011-04-12T01:34:43+00:00

@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?

bklynman01 2 Junior Poster in Training · Answer 11 · 2011-04-12T01:46:57+00:00

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.

BitBlt 452 Practically a Master Poster Featured Poster · Answer 12 · 2011-04-12T02:32:22+00:00

Not enough information for me to go on...can you post the SQL statement you have already done?

bklynman01 2 Junior Poster in Training · Answer 13 · 2011-04-12T02:40:06+00:00

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
END

BitBlt 452 Practically a Master Poster Featured Poster · Answer 14 · 2011-04-12T05:10:37+00:00

Okay, 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 #temp1

Now 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!

bklynman01 2 Junior Poster in Training · Answer 15 · 2011-04-12T18:05:16+00:00

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!

Akash Saikia 9 Junior Poster · Answer 16 · 2011-04-12T18:10:53+00:00

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 :)

BitBlt 452 Practically a Master Poster Featured Poster · Answer 17 · 2011-04-12T20:54:44+00:00

@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. ;)

bklynman01 2 Junior Poster in Training · Answer 18 · 2011-04-13T01:01:02+00:00

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. :)

desired results from ORDER BY

Recommended Answers Collapse Answers

All 23 Replies

Recommended Answers