Beginner Order By question....

bphein1980 · 07-26-2006, 02:42 AM

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.

Kid Charming · 07-26-2006, 02:49 AM

Try

ORDER BY yourcolumn+0

bphein1980 · 07-26-2006, 02:53 AM

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

Kid Charming · 07-26-2006, 03:02 AM

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.

guelphdad · 07-26-2006, 04:42 AM

The small difference being the latter will only run on mysql, whereas the first is standard sql and will run across any db.