Order By Trouble

[skaterdav85]

So I have a select menu populated with addresses from a database. But when i do ORDER BY, it puts 710 W. 30th St after 1211 W.30th St because its only doing it by the first number. Any way to get around this? Thanks!

[Synook]

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

[skaterdav85]

im using ColdFusion tho. theres no way to get around this just in sql?

[inktherapy]

I think your cell property is in varchar, it will sort by its character property not numerical. try changing it to numerical though your next problem will be the text char. hmmm?!

[Synook]

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

[boen_robot]

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.

[alexnofue]

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.

[aalbetski]

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