how to control the sort order for strings
Posted: Sun Aug 08, 2004 3:37 am
Hello
I have the following table definition:
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:
For some reason, I get all the empty names before the non-empty ones (and the same is true for the description)
Example:
should give
Help would be greatly appreciated
Jason
I have the following table definition:
Code: Select all
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;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 all
select *
from test_table
order by Name, Description;Example:
Code: Select all
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');Code: Select all
ID Name Description
1 a 333
4 a 555
5 b 222
2 b
3 111Help would be greatly appreciated
Jason