Flash Website Builder- Trendy Site Builder is a Flash Site Building tool that helps users build stunning websites. Check Out Custom Custom Logo Design by LogoBee. Website Design and Free Logo Templates available.
 CodingForums.com ORDER BY the last number of characters

Before you post, read our: Rules & Posting Guidelines

Enjoy an ad free experience by logging in. Not a member yet? Register.
 11-09-2011, 02:39 AM PM User | #1 elabuwa New Coder   Join Date: Oct 2011 Posts: 20 Thanks: 1 Thanked 0 Times in 0 Posts ORDER BY the last number of characters Hi guys, I have a table that stores an ID number in the below method. Student ID number to be exact. student_id 09/09/PG-5/117 09/09/NR-5/120 09/09/G1-1/119 09/09/PG-6/118 Desired Result 09/09/PG-5/117 09/09/PG-6/118 09/09/G1-1/119 09/09/NR-5/120 I'd like to show them in the order of the last three numbers OR in the order of the numbers that appear after the last "/" character. Is there a way make my SQL statement to get the desired result, or do I have to record them in a temp table and pull records from that? Cheers
 11-09-2011, 03:10 AM PM User | #2 Old Pedant Supreme Master coder!     Join Date: Feb 2009 Posts: 24,955 Thanks: 75 Thanked 4,308 Times in 4,275 Posts Sure. Easy. For last 3 characters: Code: `ORDER BY RIGHT(id,3)` For a *number* after the slash (and allowing the number to be any length but wanting them sorted *as* numbers), it's more complex but doable: Code: `ORDER BY CONVERT( REVERSE( LEFT( REVERSE(id), INSTR( REVERSE(id), '/' )-1 ) ), DECIMAL )` I'll explain that one. REVERSE(id) will take "09/09/PG-5/117" and convert it to "711/5-GP/90/90" INSTR( "711/5-GP/90/90", '/' ) will return 4, the position of the first / in that string LEFT( "711/5-GP/90/90", 4-1 ) thus gets us "711" Which we REVESE again to get 117. And we can then convert 117 to a number (decimal). And now "09/09/XY-1/1137" *will* come out as larger than "09/09/AB-4/73" which it wouldn't if we just did ORDER BY the *strings* "1137" and "73". You pays your money and takes your choice. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.
 11-09-2011, 03:56 AM PM User | #3 elabuwa New Coder   Join Date: Oct 2011 Posts: 20 Thanks: 1 Thanked 0 Times in 0 Posts holly smokessss. Thanks oldii. Will check and let you know.

 Bookmarks

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off Forum Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home :: Client side development     JavaScript programming         DOM and JSON scripting         Ajax and Design         JavaScript frameworks         Post a JavaScript     HTML & CSS     XML     Flash & ActionScript         Adobe Flex     Graphics and Multimedia discussions     General web building         Site reviews         Building for mobile devices :: Server side development     Apache configuration     Perl/ CGI     PHP         Post a PHP snippet     MySQL         Other Databases     Ruby & Ruby On Rails     ASP     ASP.NET     Java and JSP     Other server side languages/ issues         ColdFusion         Python :: Computing & Sciences     Computer Programming     Computer/PC discussions     Geek News and Humour Web Projects and Services Marketplace     Web Projects         Small projects (quick fixes and changes)         Medium projects (new script, new features, etc)         Large Projects (new web application, complex features etc)         Unknown sized projects (request quote)         Vacant job positions         Looking for work/ for hire         Project collaboration/ partnership         Paid work offers and requests (Now CLOSED)     Career, job, and business ideas or advice     Domains, Sites, and Designs for sale         Domains for sale         Websites for sale         Design templates and graphics for sale :: Other forums     Member Offers     Forum feedback and announcements

All times are GMT +1. The time now is 07:06 AM.