how to control the sort order for strings

[jasongr]

Hello

I have the following table definition:

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:

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:

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

ID         Name         Description
1          a               333
4          a               555
5          b               222
2          b
3                          111

Help would be greatly appreciated
Jason

[tserbis]

Try order by if('' = Name, 0xFF, Name); or something like...

[jasongr]

I got it work by using:

order by case when Name='' then 1 else 0 end
     	, Name
     	, case when Description='' then 1 else 0 end
             , Description

[r937]

I got it work ..

nice code, eh

[jasongr]

If it wasn't clear, then it was r937 who helped me with the solution.

thanks r937