Order by numbers in proper sequence

Question

Post reply

Order by numbers in proper sequence

dejanette.varnado

Right there with Babe

Points: 718
More actions
December 17, 2008 at 11:13 am

#72316

I have a query where the datatype I'm ordering by is varchar(10) but when I use the ORDER BY the values come out like this: How do I work around this? Thanks!
100
10000
10060
10077
10077
101

Viewing 15 posts - 1 through 15 (of 52 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

If you are sure that the values are all numeric, you can do this:

ORDER BY

CAST(YourColumn as INT)

dejanette.varnado Right there with Babe Points: 718 More actions · Answer 2

The field is alpha numeric. When I tried your solution here is the error message I received:

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value 'R00190' to data type int.

Is there another datatype I can convert it to that will work? Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Okay, I did say if you knew if your data was ALL numeric. You will need to analysis your data first and see what you have. Do you have any data that looks like this 0R00012?

Once you know your data, you can look at possibile solutions.

Based on what you have provided so far, I'm not willing to throw out another suggestion yet. I'll wait until you can tell me more about your data first.

Sergiy SSC Guru Points: 110207 More actions · Answer 4

dejanette.gordon (12/17/2008)
I have a query where the datatype I'm ordering by is varchar(10) but when I use the ORDER BY the values come out like this: How do I work around this? Thanks!

There is a lot of solutions.

But it's not clear what to solve.

Can you ask a PROPER QUESTION first?

What is the order of values you want to return?

Lynn gave you perfect answer on your question.

If it does not work then your question was incorrect.

Please fix it.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

Dejanette,

It would be a huge help to us if you'd post the actual data you're trying to sort in a readily consumable format along with a table creation statement. Please see the link in my signature for how to do that. Thanks.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Well, not knowing what you are working with, and deciding to take a swag at it, try out the following code and see if that gets you going in the right direction. I would recommend reading the article that Jeff suggested (and you will also find a link to below in my signature block as well). The guidelines it provides for posting questions will greatly benefit you and us when you have other questions.

We will also be able to test our code which further benefits you as well.

create table #MyTest (

TestID varchar(10) not null,

TestData varchar(100) not null

);

insert into #MyTest (

TestID,

TestData

)

select 'R000234','Some data' union all

select '100','Some more data' union all

select '101','Some more data again' union all

select '1000','And more data' union all

select 'U1023','and even more data';

select

*

from

#MyTest

order by

TestID;

select

*,

case when left(TestID,1) like '%[A-Za-z]%'

then left(TestID,1) + right('0000000000' + substring(TestID,2,len(TestID) - 1), 9)

else right('0000000000' + TestID, 10)

end

from

#MyTest

order by

case when left(TestID,1) like '%[A-Za-z]%'

then left(TestID,1) + right('0000000000' + substring(TestID,2,len(TestID) - 1), 9)

else right('0000000000' + TestID, 10)

end;

drop table #MyTest;

dejanette.varnado Right there with Babe Points: 718 More actions · Answer 7

Thanks for the replies!! I do apologize for not posting my question in the proper format. It's my first time posting, although I'm not looking for a pass based on my own ignorance of how things work around here. Here is my attempt at asking the question properly:

How do you sort alphanumeric data in sequential order? I need the data to go from:

100

101

102

etc...

instead of what is posted in the original post. In the meantime, I will read through the link on how to post properly, however I would appreciate if anyone would be willing to help me figure out this problem if I have communicated the issue properly. Much Appreciated! DG

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

That is essentially the question you asked originally. The problem was that the information you showed did not match reality. You showed values that were all numeric. If that is the case, then what I gave you originally will work.

The problem turned out that you have non-numeric data in the alphanumeric field, so my solution failed. To determine how to sort your data, you have to understand the data. This means knowing the range of values contained in the field. Obviously, it isn't purely numeric data stored as characters. If the data is mostly numeric, with non-numeric values such as R1000 (a single leading character), then the solution I just posted would be a viable alternative.

Sergiy SSC Guru Points: 110207 More actions · Answer 9

dejanette.gordon (12/17/2008)
How do you sort alphanumeric data in sequential order? I need the data to go from:
100
101
102
etc...

The answer is:

ORDER BY AlphanumericValue

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 10

dejanette.gordon (12/17/2008)
Thanks for the replies!! I do apologize for not posting my question in the proper format. It's my first time posting, although I'm not looking for a pass based on my own ignorance of how things work around here. Here is my attempt at asking the question properly:
How do you sort alphanumeric data in sequential order? I need the data to go from:
100
101
102
etc...
instead of what is posted in the original post. In the meantime, I will read through the link on how to post properly, however I would appreciate if anyone would be willing to help me figure out this problem if I have communicated the issue properly. Much Appreciated! DG

Ummmm.... read the link on how to post properly first. And, Lynn is correct... you haven't posted data that represents your real data.

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 11

Sergiy (12/17/2008)
dejanette.gordon (12/17/2008)
How do you sort alphanumeric data in sequential order? I need the data to go from:
100
101
102
etc...
The answer is:
ORDER BY AlphanumericValue

Now, THAT's entertainment! 😀

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

dejanette.varnado Right there with Babe Points: 718 More actions · Answer 12

:hehe: Thanks for the laughs...my future posts on this forum will not be subpar.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

dejanette.gordon (12/18/2008)
:hehe: Thanks for the laughs...my future posts on this forum will not be subpar.

I'm glad to see that you have a sense of humor! It is needed around these parts at times! :w00t:

dejanette.varnado Right there with Babe Points: 718 More actions · Answer 14

It sure is...it can be downright grueling and discouraging for a newbie if one takes it personally. But hey, my post was kind of simple and "cheesy" based on how it was supposed to look. I didn't know any better, but I trust me, you will NEVER see me post without following the standards 😛 I was cracking up at the last post from Jeff about entertainment...LOL You've gotta take it all in stride though. This will only help me out as well as the next newbie (not sure if you get many around here??) from being made an example of...still LOL at myself.