Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Feb 2005
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Beginner Order By question....

    I have a column in a table that is for weight classes (like in wrestling) that goes something like....

    67.5
    75
    82.5
    100
    110
    125
    140
    140+

    My problem is that I cannot store them as INT (or a number) because of the plus sign for the last weight class. So, when I have them ordered (ORDER BY weight ASC) it sorts them wrong.... sorts everything by the first number (like alpha if it was sorting text because it is stored as VARCHAR).

    How can I get around this? I have to have the plus sign in the last record and INT or FLOAT will not accept the plus sign. Like I said, I'm a beginner, so I'm sure I am missing something very simple.

    Thanks for helping me out.

  • #2
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try

    ORDER BY yourcolumn+0

  • #3
    Regular Coder
    Join Date
    Feb 2005
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for the reply, I changed my code but it did not effect how they were ordered.

    Im still searching Google, but I just cant seem to find the answer.

    NEVERMIND - I put the "+0" in the wrong spot. Oopsie! Thank you very much for the help. Consider this problem a closed case.

    Thanks for the quick help

  • #4
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Forget my first post and try this:

    Code:
    ORDER BY
     CASE yourcolumn
      WHEN '140+' THEN 1
      ELSE 0
     END
    ,yourcolumn
    or

    Code:
    ORDER BY
     FIELD(yourcolumn,'140+')
    ,yourcolumn
    They're both equivalent.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    The small difference being the latter will only run on mysql, whereas the first is standard sql and will run across any db.


  •  

    Posting Permissions

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