www.webdeveloper.com
Results 1 to 6 of 6

Thread: [RESOLVED] Alphanumeric Sort on a select

  1. #1
    Join Date
    May 2009
    Posts
    226

    resolved [RESOLVED] Alphanumeric Sort on a select

    I am selecting names of boardrooms and some of them start with numbers and some with letters. I would like the Alpha names to show first and then the numeric names.

    PHP Code:
    $sql "SELECT * FROM directory WHERE name LIKE '%Meeting Room' ORDER BY name"
    Here is how the list appears now:
    19 NW Meeting Room
    19 SE Meeting Room
    20 NW Meeting Room
    20 SE Meeting Room
    21 NW Meeting Room
    21 SW Meeting Room
    22 NW Meeting Room
    22 SE Meeting Room
    Arnoldi Meeting Room
    Beatty Meeting Room
    Calvin Meeting Room
    Godfrey Meeting Room
    Lewtas Meeting Room
    Thomson Meeting Room
    Wilson Meeting Room

    This is how I would like them to show:
    Arnoldi Meeting Room
    Beatty Meeting Room
    Calvin Meeting Room
    Godfrey Meeting Room
    Lewtas Meeting Room
    Thomson Meeting Room
    Wilson Meeting Room
    19 NW Meeting Room
    19 SE Meeting Room
    20 NW Meeting Room
    20 SE Meeting Room
    21 NW Meeting Room
    21 SW Meeting Room
    22 NW Meeting Room
    22 SE Meeting Room

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,923
    Code:
    select *, CASE WHEN  name REGEXP '^[[:digit:]]' THEN 1 ELSE 0 END AS sort_flag
    FROM directory WHERE name LIKE '%Meeting Room' ORDER BY sort_flag, name
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    May 2009
    Posts
    226
    that work amazing - any chance you could explain it to me or direct me to where I could learn about this

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,923
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    778
    Another way it could be done without regular expressions or a case statement:

    Code:
    SELECT * FROM directory WHERE name LIKE '%Meeting Room' ORDER BY FIND_IN_SET(LEFT(name, 1), '0,1,2,3,4,5,6,7,8,9'), name
    JavaScript: Learn | Validate | Compact

  6. #6
    Join Date
    May 2009
    Posts
    226
    Thank you, this makes sense too

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles