Thanks - this is even better - I will give it a try.
-----Original Message----- From: Cal Evans [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 6:19 AM To: Norris, Joseph; Mysql_List (E-mail) Subject: RE: WoW!!!! SQL is something! select req_number, right(concat('00',trim(req_number)),6) as sort_req, case from req order by sort_req; Haven't tried it but it should work. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -----Original Message----- From: Norris, Joseph [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:08 PM To: Mysql_List (E-mail) Subject: FW: WoW!!!! SQL is something! I had a problem that required a proper sort of the following: IT66 ITD38 IT68 ITD39 ITD40 IT69 ITD55 ITD56 IT72 ITD57 ITD58 ITD59 ITD60 IT73 ITD73 ITD67 IT74 ITD69 ITD70 IT78 IT79 IT83 ITD71 IT81 ITD75 ITD76 IT84 ITD96 ITD97 ITD98 ITD99 ITD100 ITD101 ITD102 ITD103 ITD104 ITD105 Because of the different lengths, this was a nightmare for "order by" and so it led me to my first experiments with case and I go the following: select req_number, case when (length(req_number)=4) then concat('00', req_number) when (length(req_number)=5) then concat('0', req_number) when (length(req_number)=6) then req_number END as sort_req from req order by sort_req; Now there might be other ways to handle this and I would love to hear about them but I thought that this taught me something about sql that I had not understood until now. Just would like to give a little back. Thanks to all. --------------------------------------------------------------------- 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
