Sorting UK Postcodes (WAS Sorting Results)
Hi, What about if you want to sort UK Postcodes ? They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? Cheers, Damien COLA - 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: Sorting UK Postcodes (WAS Sorting Results)
Sorry, I forgot to say that postcode can be one or 2 letters in front of the numbers. -Message d'origine- They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? - 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: Sorting UK Postcodes (WAS Sorting Results)
Damien -- What about: select substring(code, 1, 1) as letter, substring(code, 2, 2) + 0 as number from epost order by 2 +++ | letter | number | +++ | E | 1 | | E | 2 | | E | 3 | | E | 4 | | E | 10 | | E | 11 | | E | 12 | | E | 13 | +++ 8 rows in set (0.00 sec) Then just use your scripting language to join the letter and number back together. I tried mysql concat() but it makes the whole thing into a wrong-sorted string again. FWIW -- Doug At 10:17 PM 12/12/2002 +0100, Alliax wrote: Hi, What about if you want to sort UK Postcodes ? They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? Cheers, Damien COLA - 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: Sorting UK Postcodes (WAS Sorting Results)
Hi, Could you seperate the letters into one column and the numbers in another... Then you could group by the Alphabetical column, and order by the numerical column... Regards, Chris Stark -Original Message- From: Alliax [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 4:53 PM To: MySQL List Subject: RE: Sorting UK Postcodes (WAS Sorting Results) Sorry, I forgot to say that postcode can be one or 2 letters in front of the numbers. -Message d'origine- They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? - 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: Sorting UK Postcodes (WAS Sorting Results)
At 22:53 12/12/2002 +0100, Alliax wrote: Sorry, I forgot to say that postcode can be one or 2 letters in front of the numbers. -Message d'origine- They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? If sorting by number is important, then split them on input and store them in two separate files. But I don't really know why you'd want to sort them numerically anyway - UK postcodes aren't sequential in operation: E1 is not necessarily adjacent to E2 on the map. So having a sequence of E1,E10,E2, etc is no less applicable than E1,E2,E10 would be. Mark - 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: Sorting UK Postcodes (WAS Sorting Results)
Thank you and Yes, separating the postcodes into field letters and numbers may be the only way to be able to order a sequence like that: CV1,CV2,..,CV10,.. It would also work since there could as many letters as needed Anyone has a simple query for odering sequences correctly ? Cheers, Damien COLA -Message d'origine- Could you seperate the E's in one column and the numbers in another... - 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: Sorting UK Postcodes (WAS Sorting Results)
Hi, yes, that would do, except that I don't know how many letters are in front of the numbers. And since they're not the same letters perhaps ORDER by 1,2 would have done it perfectly, if only we could substring() intelligently first. Cheers, Damien COLA -Message d'origine- SQL: select substring(code, 1, 1) as letter, substring(code, 2, 2) + 0 as number from epost order by 2 - 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: Sorting UK Postcodes (WAS Sorting Results)
Sorting by number is not important, it was more a general question about sequences of variousnameXXX and how to order them correctly with a simple query. Splitting in 2 fields seems the only way then. For the postcode it's just to provide the user with an easy lookup table, but it's not *that* important since the way it's ordered now is the same as many (if not all) file systems do. If sorting by number is important, then split them on input and store them in two separate files. But I don't really know why you'd want to sort them numerically anyway - UK postcodes aren't sequential in operation: E1 is not necessarily adjacent to E2 on the map. So having a sequence of E1,E10,E2, etc is no less applicable than E1,E2,E10 would be. - 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
