MySQL natural number order (ORDER BY)

[atw]

Hi,

I have a DB with lots of property information in it and I need to return all the properties and order them by price. However, I at the moment my query is sorted incorrectly:
143555
145666
40777
30888

They should be more like:
30888
40777
143555
145666

How would I go about this? My existing query is as follows:

SELECT * FROM <table> WHERE location = '<location>' AND <table>.availiable = 'yes' 
  OR property_type = '<property_type>' AND <table>.availiable = 'yes' 
ORDER BY price ASC

Thanks in advance...

[atw]

Oooh, I have just figured it out for myself...

You simply add "+ 0" (that's a zero) between the sort field and ASC, so my query becomes:

SELECT * FROM <table> WHERE location = '<location>' AND <table>.availiable = 'yes' 
  OR property_type = '<property_type>' AND <table>.availiable = 'yes' 
ORDER BY price + 0 ASC

[TiGGi]

what's the type for your price field?

[chazzy]

actually, it doesn't matter the type. This is a known bug in mysql where it orders based on value length as well.

[silverbullet24]

i've had a similiar problem with a property database. the VERY CRUMMY database admin who set it up set EVERY field to CHAR(255) including the price field. well that didn't sort correctly obviously, so in my sql statement i just converted the price field

SELECT * FROM PROPS ORDER BY CONVERT(INT,PRICEFLD) ASC

works great for me

[atw]

@TiGGi: Ah, I see your point. It is a VARCHAR(20), you (I assume are thinking it would be better as a FLOAT -- and so am I now)... The field cannot be an INT as there might be ******.99 (or something) going into it (even though there isn't at the mo.) -- I know if I changed it, a property would appear at ******.99 (i'm not very lucky)

@chazzy: So does that mean my "+ 0" has told MySQL to see the data as zero length (i.e. ignore the value's length)?

@silverbullet24: Lol, I didn't even know an SQL query could do that; I assumed that would involve PHP gathering all the info into a massive array and than sorting that first (not something I wanted to deal with -- I'm still very much a newbie )

PS. Thanks for replying

[chazzy]

actually, the +0 converts it to a number. the issue in question has to do with sorting strings - it does it oddly with varying lengths.

if you have decimals, you can use the float or double datatypes. not varchar.

[atw]

actually, the +0 converts it to a number. the issue in question has to do with sorting strings - it does it oddly with varying lengths.

if you have decimals, you can use the float or double datatypes. not varchar.

I think I'll leave it alone; it seems to work and "if it ain't broke, don't fix it"

Thank you all for replying so quickly. I'll try not to make this mistake again in the future

[chazzy]

actually, i think you should change it. what's stopping someone from entering a word in this field?

[atw]

actually, i think you should change it. what's stopping someone from entering a word in this field?

Hmmm, that's a good point.

Will the existing data survive the conversion intact?