shaidermask Posted March 28, 2008 Share Posted March 28, 2008 I have a problem... I want to sort and print records.. say for example... 1-3 1-10 1-11 1-12 1-1 1-2 when i sorted it by ascending order.. the output is: 1-10 1-11 1-12 1-1 1-2 1-3 the right output should be... 1-1 1-2 1-3 1-10 1-11 1-12 Please help me. Thank you... Quote Link to comment Share on other sites More sharing options...
moon 111 Posted March 28, 2008 Share Posted March 28, 2008 It is sorting the information character by character. 1-10 1-11 1-12 1-1 1-2 1-3 You need to sort everything after the dash though I'm not sure how to do that... Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted March 28, 2008 Share Posted March 28, 2008 There are several ways of making this "work", but all of them will be slower than if these were stored as numbers (they are string now.) So, if you are expecting a lot of data, it would be best to store them as numbers. Is there a reason why you are storing them with the dash (instead of just formatting the output to add the dash when you display the information)? Quote Link to comment Share on other sites More sharing options...
benjaminbeazy Posted March 28, 2008 Share Posted March 28, 2008 u could also natsort, but that may affect your execution time with a lot of records... Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted March 28, 2008 Share Posted March 28, 2008 They way they are stored now, it is impossible to do any greater-than/less-than comparisons or sorts directly in the query without passing every value through a mysql function(s) to get the values into a form that are comparable or sortable or even slower yet, fetching all the rows and using php code to accomplish anything with them. Quote Link to comment Share on other sites More sharing options...
aschk Posted March 28, 2008 Share Posted March 28, 2008 If the format is x-xx then I suggest breaking it up into 2 different columns (both of which are INT), then you can ORDER BY ColA, ColB, et voila, problem resolved. Quote Link to comment Share on other sites More sharing options...
mwasif Posted March 28, 2008 Share Posted March 28, 2008 If the format is x-xx then I suggest breaking it up into 2 different columns (both of which are INT), then you can ORDER BY ColA, ColB, et voila, problem resolved. If it is not required to store the values in single column then aschk solution is best. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 28, 2008 Share Posted March 28, 2008 If the format is x-xx then I suggest breaking it up into 2 different columns (both of which are INT), then you can ORDER BY ColA, ColB, et voila, problem resolved. If it is not required to store the values in single column then aschk solution is best. If not, you can use an expression... but that's slow too. Quote Link to comment Share on other sites More sharing options...
shaidermask Posted March 31, 2008 Author Share Posted March 31, 2008 hi guys, I managed to create a script but I still have problems in here... here it is... $sql = mysql_query( "SELECT * FROM tblPlantilla ORDER BY cast(left(itemNumber+ '-0', instr(itemNumber+ '-0', '-')) as unsigned) asc, cast(mid(itemNumber+ '-0', instr(itemNumber+ '-0', '-')+1) as unsigned) asc"); I have this part which is wrong.. 1 2 3 4-2 4-1 5 ... 27-1 27-2 27-3 28-1 28-2 28-3 29-3 29-1 29-2 30 ... 124-1 124-2 125 126 127-2 127-1 127-2 127-1 128-1 128-2 128-1 129 ... The output looks almost perfect but I wonder why some parts are not sorted right Hope you can help me solve this... thanks alot! Quote Link to comment Share on other sites More sharing options...
fenway Posted March 31, 2008 Share Posted March 31, 2008 Could you include each of those expressions in the column list? Quote Link to comment Share on other sites More sharing options...
shaidermask Posted April 1, 2008 Author Share Posted April 1, 2008 Could you include each of those expressions in the column list? What do you mean by that, sir? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 1, 2008 Share Posted April 1, 2008 Could you include each of those expressions in the column list? What do you mean by that, sir? I mean I'd like to see what those expression return. Quote Link to comment Share on other sites More sharing options...
shaidermask Posted April 2, 2008 Author Share Posted April 2, 2008 Could you include each of those expressions in the column list? What do you mean by that, sir? I mean I'd like to see what those expression return. This is part of the output... 1 2 3 4-2 4-1 5 ... 27-1 27-2 27-3 28-1 28-2 28-3 29-3 29-1 29-2 30 ... 124-1 124-2 125 126 127-2 127-1 127-2 127-1 128-1 128-2 128-1 129 I wonder why some are parts are not sorted correctly. Please help... I really need a solution for this asap. Thank you. Quote Link to comment Share on other sites More sharing options...
fenway Posted April 2, 2008 Share Posted April 2, 2008 Sigh... run this: SELECT cast(left(itemNumber+ '-0', instr(itemNumber+ '-0', '-')) as unsigned) as part1, cast(mid(itemNumber+ '-0', instr(itemNumber+ '-0', '-')+1) as unsigned) as part2, * FROM tblPlantilla Quote Link to comment Share on other sites More sharing options...
shaidermask Posted April 3, 2008 Author Share Posted April 3, 2008 Sigh... run this: SELECT cast(left(itemNumber+ '-0', instr(itemNumber+ '-0', '-')) as unsigned) as part1, cast(mid(itemNumber+ '-0', instr(itemNumber+ '-0', '-')+1) as unsigned) as part2, * FROM tblPlantilla I run it but this is a portion of the output.. part1 part2 0 0 0 1 0 10 0 10 0 100 0 101 0 101 0 102 0 102 part1 column returns 0 values... Please... Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted April 3, 2008 Share Posted April 3, 2008 By not structuring your data in a form that can be used directly in a query (see replies #5, #6, and #7), in addition to resulting an exceedingly slow query every time it is executed, using the current method will take an exceedingly long time to produce working code (it has been 5+ days since the thread was started.) As was stated previously, by structuring the data in an easy to use format, ordering would simply be - ORDER BY colA, colB and outputting the value would be - SELECT CONCAT(colA, '-', colB) as the_name_you_want To get your data into that format in the table and changing your application code to insert it will be a one time operation that will save you a lot of headaches in the future. Quote Link to comment Share on other sites More sharing options...
fenway Posted April 3, 2008 Share Posted April 3, 2008 What do you think the first operand to LEFT() is doing? You can't string concat that way. 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.