SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to order alphabetically EXCEPT for one item?

    Greetings,

    I know how to order a list of items alphabetically:
    Code:
    $sql = "SELECT name FROM categories ORDER BY name";
    It produces a list like this:
    A...
    B...
    C...
    Food
    Other
    Tools
    Water
    X...
    Y...
    Z...

    I would like the category name "Other" to appear at the very bottom of the list as it should be chosen if the other items of the list do not apply. How can this be done?

    Thanks

  2. #2
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT name
    FROM categories
    ORDER BY name='Other' ASC, name ASC
    aaron-fisher.com - PHP articles and more

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    aamonkey, although your code works, it relies on a quirk of mysql whereby an equality comparison, which evaluates true or false, is interpreted as 1 or 0

    i think for the benefit of simplicity and clarity, an explicit CASE expression, which is standard SQL and will work in any database system, is better --
    Code:
    ORDER 
        BY CASE WHEN name = 'Other' 
                THEN 1 -- last
                ELSE 0 -- first
            END ASC
         , name ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot for the help r937. I haven't come across CASE before so this is some neat stuff I can study about as well!

    Kind regards

  5. #5
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's nice r937 - didnt know about that syntax
    aaron-fisher.com - PHP articles and more

  6. #6
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    There is nothing unstandard in sorting by a boolean value. FALSE is defined as being less than TRUE. It is not as portable between different DBMS as the case expression though.


Bookmarks

Posting Permissions

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