Re: Basic Q's: Numerical Sorting
In the last episode (Jul 09), CVIOG at UGA said: > I have a fairly basic question: How do I sort > numerically? Normally when I query using "ORDER BY > (field)", it orders by the first digit (i.e. 1, 10, 2, > 21, 3, 32) rather than by number (1, 2, 3, 10, 21, > 32). Either alter the table to make that field an INTEGER column, or "ORDER BY field+0" to force mysql to internally convert the field to a number before sorting. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Basic Q's: Numerical Sorting
Here is the solution I posted about t months ago. I would also add, this solution can handle both text and numbers in the same field...: my boss figured out a nice solution to this ORDER BY IF(ABS(Item) > 0,LPAD(Item,9,'0'),Item) This will pad numbers ( ok it might not do so hot with DECIMAL, but it can be fixed to work I supposed) with 0 So it should sort 1 2 00010 00022 00050 00230 instead of 1 10 2 22 230 50 And it will skip padding text cause I guess the ABS of text is 0, and the "numbers" and words will appear separated from each other My boss is smart :) > -Original Message- > From: CVIOG at UGA [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 09, 2002 4:22 PM > To: [EMAIL PROTECTED] > Subject: Basic Q's: Numerical Sorting > > > I have a fairly basic question: How do I sort > numerically? Normally when I query using "ORDER BY > (field)", it orders by the first digit (i.e. 1, 10, 2, > 21, 3, 32) rather than by number (1, 2, 3, 10, 21, > 32). > > Thanks for any help > Dave > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Basic Q's: Numerical Sorting
It looks like your field is a text field rather than BIGINT or some numerical field. Changing the field type should solve your problem. Serge. - Original Message - From: "CVIOG at UGA" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, July 09, 2002 4:22 PM Subject: Basic Q's: Numerical Sorting > I have a fairly basic question: How do I sort > numerically? Normally when I query using "ORDER BY > (field)", it orders by the first digit (i.e. 1, 10, 2, > 21, 3, 32) rather than by number (1, 2, 3, 10, 21, > 32). > > Thanks for any help > Dave > > __ > Do You Yahoo!? > Sign up for SBC Yahoo! Dial - First Month Free > http://sbc.yahoo.com > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
