Strahan Posted July 8, 2015 Share Posted July 8, 2015 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! 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. 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! 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
Archived
This topic is now archived and is closed to further replies.