Sorting the last 2 values in a name

Posted on 2012-08-27
Last Modified: 2012-08-28
Hello everyone, I am assuming someone has run into this before, I have a bunch of items who's last two characters are -A,-B,-C.....-Z,AA,AB,AC,AD in a data table the problem is any kind of simple sort in the SQL using Order By Right(myname,2)
gives me back -A,AA,AB,AC,AD,-B,-C...-Z) .
Is there a good way of doing what I want through SQL or do I have to go through the whole record-set manually and sort them into an array?
Question by:dsulkar
    LVL 61

    Assisted Solution

    Depending on the platform...

    This would work for Access:

    Order By Replace(Right(myname,2),"-","")
    LVL 6

    Accepted Solution

    Try the following:

    ORDER BY LEN(REPLACE(RIGHT(myname, 2), '-', '')), RIGHT(myname, 2)

    Open in new window

    LVL 6

    Author Comment

    Both of you were right there, I am a programmer, not a full time DBA. I knew there was an easy way. fhlio_admin your answer was complete since just removing the "-" does not change the sort.
    Order By Replace(Right(myname,2),"-","") still gave me A,AA,AB,AC,B,C,D...
    so the second ORDER RIGHT(myname, 2) was necessary.
    Thanks to both of you
    LVL 35

    Expert Comment

    I know the question is already accepted but what DB is this based on? You could also try this... (since the DB mustn't be sorting on the character value, otherwise the '-' WOULD come before 'A', but maybe the sort order that your database is using WILL put the '0' before the 'A')

    ORDER BY REPLACE(RIGHT(myname, 2), '-', '0')

    Open in new window

    If it works it should be more efficient (probably only a very minor difference, but every little bit helps)
    LVL 6

    Author Comment

    It is just a standard Access 2003 database, thanks for the tips

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    A short article about problems I had with the new location API and permissions in Marshmallow
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now