Order by

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Order by

Order by

Rate Topic

Display Mode

Topic Options

Author

Message

wacton

Posted Monday, January 14, 2008 10:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 28, 2010 1:38 AM
Points: 3, 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

Post #442542

Matt Miller (#4)

Posted Monday, January 14, 2008 10:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:56 PM
Points: 6,997, Visits: 13,941

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?

Post #442552

Matt Miller (#4)

Posted Monday, January 14, 2008 10:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:56 PM
Points: 6,997, Visits: 13,941

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?

Post #442554

wacton

Posted Monday, January 14, 2008 10:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 28, 2010 1:38 AM
Points: 3, 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

Post #442555

Matt Miller (#4)

Posted Monday, January 14, 2008 10:45 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:56 PM
Points: 6,997, Visits: 13,941

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?

Post #442561

Greg Jennings

Posted Thursday, January 21, 2010 7:02 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 1:08 PM
Points: 23, Visits: 130

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.

Post #851223

« Prev Topic | Next Topic »

Permissions