Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,202
    Thanks
    80
    Thanked 4,563 Times in 4,527 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.

  • #3
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •