...

View Full Version : Beginner Order By question....



bphein1980
07-26-2006, 03: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, 03:49 AM
Try

ORDER BY yourcolumn+0

bphein1980
07-26-2006, 03: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, 04:02 AM
Forget my first post and try this:



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


or



ORDER BY
FIELD(yourcolumn,'140+')
,yourcolumn


They're both equivalent.

guelphdad
07-26-2006, 05: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. :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum