SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Order by


Order by

Author
Message
wacton
wacton
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 11
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
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29269 Visits: 19002
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)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29269 Visits: 19002
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
wacton
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 11
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)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29269 Visits: 19002
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
Greg Jennings
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 169
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search