gin Posted January 19, 2012 Share Posted January 19, 2012 I have a table: SELECT * FROM table ORDER BY sort; +------+ | sort | +------+ | 1 | | 2 | | aaa | | bbb | | ccc | +------+ I want to make the numbers appear after the letters, like so: +------+ | sort | +------+ | aaa | | bbb | | ccc | | 1 | | 2 | +------+ Please advise. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 19, 2012 Share Posted January 19, 2012 ORDER BY FIELD(sort, sort) ASC. Bizarre casting. Quote Link to comment Share on other sites More sharing options...
gin Posted January 19, 2012 Author Share Posted January 19, 2012 It works... for this example anyway, and I have no idea why... Could you elaborate? I'm trying to apply this to my actual query, and it's not working at all Quote Link to comment Share on other sites More sharing options...
gin Posted January 19, 2012 Author Share Posted January 19, 2012 AUGH, I apologize! I was looking at the wrong column used for sorting! It wasn't numbers vs letters at all. it was numbers vs. NULL. Below is my solution. Actual table SELECT * FROM table ORDER BY sort; +------+ | sort | +------+ | NULL | | 1 | | 2 | | 3 | +------+ Placing the null at the bottom: SELECT * FROM `table` ORDER BY (CASE WHEN sort IS NOT NULL THEN 1 ELSE 0 END) DESC; +------+ | sort | +------+ | 1 | | 2 | | 3 | | NULL | +------+ Quote Link to comment Share on other sites More sharing options...
fenway Posted January 19, 2012 Share Posted January 19, 2012 It works... for this example anyway, and I have no idea why... Could you elaborate? If you include this expression in your SELECT column list, you'll see why. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.