Jump to content

Order By Trouble


skaterdav85
 Share

Recommended Posts

MySQL has no natural sort algorithm, unfortunately. You could post-process the result with PHP (e.g. with natsort())...

Link to comment
Share on other sites

Coldfusion isn't a DBMS... is it???If you are using Oracle there is a Regex solution, search on the Stack Overflow forum.

Link to comment
Share on other sites

Similarly to what Mark Ryan said, I would suggest you separate your data into various fields, and assemble it in the Cold Fusion layer.In other words, instead of having "710 W. 30th St" and "1211 W. 30th St" in one DB field/column, have "710" and "30" in two fields/columns on one record/row, and "1211" and "30" in another record/row as those two fields/columns. Make those new fields/columns numeric, and order by them. Use Cold Fusion to assemble the pieces into the text you currently have.Alternatively, I think (but I'm not sure... I don't have enough experience with SQL) that SQL may have some functions in itself to alter the dataset once it gets sorted numerically. If there are indeed such functions, you may use them to assemble your pieces, and deliver the final text out to Cold Fusion. Keep in mind that if you use such an approach (assuming such exists), Cold Fusion won't be aware of the original data, so if there's something you want to do with the data beyond displaying it, it might be better to go with the previous approach.

Link to comment
Share on other sites

Hi, probably this isn't the solution you are looking for, but can't you order the info you just get from your DB? maybe order it when the info is in array before printing.i'm clueless in cold fusion but it must be possible to do some sorting.

Link to comment
Share on other sites

this worked for me, will fail if non-numeric data is present in the beginning of the field. It cast the data upto the first space as a number and sorts on that.SQL Server

select * from ( select 	[Address] =	'710 W. 30th St' 	union	select	'1211 W.30th St') s1order by cast(substring(Address,1,charindex(' ',Address)) as int)

result710 W. 30th St1211 W.30th St

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...