ruano84 Posted June 29, 2007 Share Posted June 29, 2007 Hi, Im writing a php application that manage the townhouses in a resort complex. Every unit has a id, and some of them have a numeric-based id, and others an alphanumeric id. When i call the select statement with the "order by" "asc" or "des" option, it orders the records like "win98", i mean: 101 102 103 104 10A101 10A102 10A103 110 111 112 , and not like "XP" (sorry by the names, but that's the order behavior) 101 102 103 104 110 111 112 10A101 10A102 10A103 , that i need. There is some way to fix this? i mean, without the need of braking the digits in separate columns. Thanks for the attention, any help would be appreciated, Atte. Alexis Rodriguez Link to comment https://forums.phpfreaks.com/topic/57746-order-by-statement-not-working-as-spected/ Share on other sites More sharing options...
Illusion Posted June 29, 2007 Share Posted June 29, 2007 try ORDER BY BINARY Link to comment https://forums.phpfreaks.com/topic/57746-order-by-statement-not-working-as-spected/#findComment-285909 Share on other sites More sharing options...
ruano84 Posted June 29, 2007 Author Share Posted June 29, 2007 Thanks, but its the same Link to comment https://forums.phpfreaks.com/topic/57746-order-by-statement-not-working-as-spected/#findComment-285916 Share on other sites More sharing options...
Illusion Posted June 29, 2007 Share Posted June 29, 2007 select * from table where col NOT LIKE '%A%' order by col UNION select * from table where col LIKE '%A%' order by col; may be that is not the easy way of doing it, may be u can try using CAST(). Guys is there any other way of doing it simply.......................... Link to comment https://forums.phpfreaks.com/topic/57746-order-by-statement-not-working-as-spected/#findComment-285944 Share on other sites More sharing options...
ruano84 Posted June 29, 2007 Author Share Posted June 29, 2007 thanks, brother, thats it!! Link to comment https://forums.phpfreaks.com/topic/57746-order-by-statement-not-working-as-spected/#findComment-285948 Share on other sites More sharing options...
fenway Posted June 29, 2007 Share Posted June 29, 2007 select * from table where col NOT LIKE '%A%' order by col UNION select * from table where col LIKE '%A%' order by col; may be that is not the easy way of doing it, may be u can try using CAST(). Guys is there any other way of doing it simply.......................... Well, mysql does a lexical sort for char columns, which is why you're getting the "win98" output, as you call it. Illusion's solution is a hack, which works for the specified input, but won't work in the general case. You could use a regex to find any non-integer values, and then use illusion's idea of the union to merge the sets together. Link to comment https://forums.phpfreaks.com/topic/57746-order-by-statement-not-working-as-spected/#findComment-286009 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.