Strahan Posted July 8, 2015 Share Posted July 8, 2015 (edited) Hi. I have a table with file data and one of the fields is "episode" for episode number. If it was just plain numbers, I'd do it int and all would be happy. However, sometimes in a folder I may have eps 1-13 then some specials which I number as SP01, SP02, etc so the episode field is varchar(10). If I have these episodes in the db (2, 1, 9, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, SP01, SP02) when I list files, I do SELECT * FROM media ORDER BY disporder,episode,filename and I get: 1 10 11 12 13 2 3 4...etc I have to make the episodes in the db as 01, 02, 03 to get the ordering right. Is there a SQL trick wherein I can get it to order those number fields in the right order, putting the alpha stuff at the bottom or would I just have to bite the bullet and zero prefix all my single digit episodes? Thanks! Edited July 8, 2015 by Strahan Quote Link to comment https://forums.phpfreaks.com/topic/297215-select-rows-and-order-by-a-text-field-as-if-its-numeric/ Share on other sites More sharing options...
mac_gyver Posted July 8, 2015 Share Posted July 8, 2015 if you use episode * 1 in your ORDER BY term, it should treat the values as numbers. also, CAST(episode as UNSIGNED) should work as well. Quote Link to comment https://forums.phpfreaks.com/topic/297215-select-rows-and-order-by-a-text-field-as-if-its-numeric/#findComment-1515800 Share on other sites More sharing options...
Strahan Posted July 11, 2015 Author Share Posted July 11, 2015 Thank you very much! Quote Link to comment https://forums.phpfreaks.com/topic/297215-select-rows-and-order-by-a-text-field-as-if-its-numeric/#findComment-1516095 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.