SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict jasongr's Avatar
    Join Date
    Jul 2004
    Location
    usa
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to control the sort order for strings

    Hello

    I have the following table definition:
    Code:
    create table test_table (
      ID INT UNSIGNED NOT NULL primary key,
      Name VARCHAR(255) NOT NULL,
      Description VARCHAR(255) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    Some of the rows in the table may contain an empty Name or Description (or both). Let me stress that the value is an empty string and not a null. Other rows may contain a non-empty Name or Description (or both).

    I would like to display all the rows in the table in the following order:
    * Order the rows by Name (non-empty names should be listed before empty ones)
    * Rows who match on the Name column should be sorted by Description (seconday sorting). Again, non-empty description should be listed before empty ones.

    The following query doesn't work:
    Code:
    select *
    from test_table 
    order by Name, Description;
    For some reason, I get all the empty names before the non-empty ones (and the same is true for the description)

    Example:
    Code:
    insert into test_table (ID, Name, Description) values (1, 'a', '333');
    insert into test_table (ID, Name, Description) values (2, 'b, '');
    insert into test_table (ID, Name, Description) values (3, '', '111');
    insert into test_table (ID, Name, Description) values (4, 'a', '555');
    insert into test_table (ID, Name, Description) values (5, 'b', '222');
    should give
    Code:
    ID         Name         Description
    1          a               333
    4          a               555
    5          b               222
    2          b
    3                          111
    Help would be greatly appreciated
    Jason

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    Ukraine, Khmelnitsky
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try order by if('' = Name, 0xFF, Name); or something like...
    Sergey Bres aka tserbis, Ukraine

  3. #3
    SitePoint Addict jasongr's Avatar
    Join Date
    Jul 2004
    Location
    usa
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got it work by using:
    Code:
    order by case when Name='' then 1 else 0 end
         	, Name
         	, case when Description='' then 1 else 0 end
                 , Description

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jasongr
    I got it work ..
    nice code, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict jasongr's Avatar
    Join Date
    Jul 2004
    Location
    usa
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If it wasn't clear, then it was r937 who helped me with the solution.

    thanks r937


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
  •