Ordering a Varchar Field... 1

[Rebies]

I have a field called 'rating' which is of the datatype Varchar. I am trying to sort it, but am having two problems. One is that when I "order by rating desc" I find that a rating of 1 or 10 is listed last because thats how it is alphabetially sorted. When there is not a rating I have the content of 'N/A' in there, and the 'N/A's show up first because alphabetially they are ordered first. (when doing a DESC order by that is)

Unfortunately I can not change how this is set up, but need to get the ordering correct.

Any ideas? I'm mostly limited to changing the "ORDER BY" clause, and not much else.

Andrew

 

[swampBoogie]

order by case
when isNumeric(rating) then cast(rating as int)
else -1 end desc

 

[Rebies]

Wow, thats intuitive. I would have never thought of doing that. Thanks for the help. I was starting to think I was up some sort of creek.



Andrew

 

[Rebies]

Oh, just for your info (or anyone else that reads this)..

You do need to check isNumeric(rating) to something, so the acutal code is:

ORDER BY case
WHEN isNumeric(rating) = 1 THEN cast(rating as int)
ELSE -1 END DESC