how to control the sort order for strings

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jasongr
Forum Contributor
Posts: 206
Joined: Tue Jul 27, 2004 6:19 am

how to control the sort order for strings

Post by jasongr »

Hello

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;
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 all

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: 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');
should give

Code: Select all

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

Help would be greatly appreciated
Jason
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

use asc and desc in your order by:

Something like this...

Code: Select all

select * 
from test_table 
order by Name asc, Description desc;
jasongr
Forum Contributor
Posts: 206
Joined: Tue Jul 27, 2004 6:19 am

Post by jasongr »

this solution doesn't meet the requirements I presented

here is the solution I have found

order by case when Name != '' then 1 else 0,
, Name
, case when Description != '' then 1 else 0,
, Description
Post Reply