www.webdeveloper.com
Results 1 to 10 of 10

Thread: MySQL natural number order (ORDER BY)

Hybrid View

  1. #1
    Join Date
    Jul 2004
    Posts
    53

    MySQL natural number order (ORDER BY)

    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:
    Code:
    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...

  2. #2
    Join Date
    Jul 2004
    Posts
    53
    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:
    Code:
    SELECT * FROM <table> WHERE location = '<location>' AND <table>.availiable = 'yes' 
      OR property_type = '<property_type>' AND <table>.availiable = 'yes' 
    ORDER BY price + 0 ASC

  3. #3
    Join Date
    Feb 2006
    Posts
    520
    what's the type for your price field?

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    actually, it doesn't matter the type. This is a known bug in mysql where it orders based on value length as well.

  5. #5
    Join Date
    Jun 2005
    Location
    WI, US
    Posts
    188
    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

  6. #6
    Join Date
    Jul 2004
    Posts
    53
    @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

  7. #7
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.

  8. #8
    Join Date
    Jul 2004
    Posts
    53
    Quote Originally Posted by 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.
    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

  9. #9
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    actually, i think you should change it. what's stopping someone from entering a word in this field?

  10. #10
    Join Date
    Jul 2004
    Posts
    53
    Quote Originally Posted by chazzy
    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?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles