Is there a way to sort a query on odd/even numbers

Posted on 2014-08-13
Last Modified: 2014-08-17
I have a database of buildings.  I would like to sort it by odd/even address number.  Give me all the odds first followed by the evens.
Question by:breeze351
    LVL 39

    Expert Comment

    by:Kyle Abrahams

    order by
    case when cast (address_number as int) % 2 = 1 then SELECT 0; 
    SELECT 1 ;
    end case

    Open in new window

    More on the case statement here:

    If you don't have address_number as its only field you can do a substring on the address column and use a char index to find the space between the number and the Street name.
    LVL 14

    Accepted Solution

    Not sure, but this might be faster than a case statement. It's cleaner, at least.

    select * 
    from buildings 
    order by address_number %2 = 0

    Open in new window

    If you want the addresses in numerical order,

    select * 
    from buildings 
    order by address_number %2 = 0, address_number

    Open in new window


    Author Closing Comment

    Thanks that works

    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

    Join & Write a Comment

    Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    The viewer will learn how to count occurrences of each item in an array.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    746 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

    12 Experts available now in Live!

    Get 1:1 Help Now