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
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
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
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
