Order by

Question

Order by

wacton

SSC Enthusiast

Points: 117
More actions
January 14, 2008 at 10:15 am

#185528

Hi,
I've got a problem with ordering my result set.
Basically I have a varchar column, the user then enters some numbers into it followed by some text. ex: "1.2.3.4 sometext" and "1.2.3.34 text"
Now the order by then sorts the result set as follows:
"1.2.3.34 text"
"1.2.3.4 sometext"
I understand why this happens. The client has told me that I can split the two fields. Having one for the numeric bit and one for the text bit.
What I would like to know is what data type would I need to use to get the numbers to sort correctly?
Or any other option for that matter.
Thanks.
Wayne

Viewing 6 posts - 1 through 5 (of 5 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

The only way you will ever get that to order correctly is to either split the numbers into separate columns, or pick a width for each of the numeric sections and zero-pad the stuff. After all - "1.2.3.4" > "1.2.3.34" is going to be true with or without the extra text after it.

so you'd need to so to something like: "001.002.003.004" which would be LESS than "001.002.003.034"

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Now - keeping in mind that you might not want to SEE the the padded version, you might be able to create a calculated field that "pads" your number sequence like I mentioned. So - the user types in "1.2.3.4" and the calculated field would then have "001.002.003.004".

You can then order by the calculated field (make it persisted and put an index on it too).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

wacton SSC Enthusiast Points: 117 More actions · Answer 3

That's what I was thinking as well, the client however, can't give me a max size for the numbers though.

I figured I might as well just go for 4 zeros in each interval, that should be enough.

Just wanted to know if there is a better way of doing it.

Thanks

Wayne

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

If they can't give you a width - then perhaps work on the "split into integers": meaning one field with the VarChar version, and four more to capture the octets.

Take a look at the PARSENAME function - should make that process easy.

declare @t varchar(20)

select @t='1.2.3.4'

select PARSENAME(@t,4),PARSENAME(@t,3),PARSENAME(@t,2),PARSENAME(@t,1)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Greg Jennings SSC Enthusiast Points: 175 More actions · Answer 5

You could parse the string and store the 4 integers in 4 INT columns, and use them to sort. Create a computed column that returns the 4 integers together in the W.X.Y.Z format.