MYSQL Sorting

[hcamelion]

I have a table similar to this:

manufacturers_name manufacturers_sort
John Doe | Doe, John
The Cream Puffs | Cream Puffs The
Jason Stark |
Joey Sparks | Sparks Joey
Sarah Banks |

I Need it to sort like this:

C:
The Cream Puffs

D:
John Doe

J:
Jason Stark

S:
Sara Banks
Joey Sparks

Basically it sorts by the sort name but if that is null it sorts by the display name. I am using this statement now:


select display_name from manufacturers order by manufacturers_sort, manufacturers_name



while ($list = mysql_fetch_array($result)) {


if($list['manufacturers_sort'] != "")
$letter = strtoupper(substr($list['manufacturers_sort'],0,1));
else
$letter = strtoupper(substr($list['manufacturers_name'],0,1));

if ($letter != $prev_row) {
$output .= $letter;
} // end if


$output .= $list['manufacturers_name'];

..............................
.............
......
..



This is puts any entries where the sort name is not null at the end of the page like this:


J:
Jason Stark

S:
Sarah Banks

C:
The Cream Puffs

D:
John Doe

S:
Joey Sparks

Anyone know how I can accomplish what I want to do.

[mcjwb]

Not sure if this will work:
select IF(manufacturers_sort=NULL, manufacturers_name,manufacturers_sort) as temp_name, display_name from manufacturers order by temp_name
Can you give it a go?

Otherwise you might need to use a tempory table or join the table to itself.

[guelphdad]

just use coalesce

ORDER BY
COALESCE(manufacturers_sort,manufacturers_name)

[hcamelion]

Not sure if this will work:
select IF(manufacturers_sort=NULL, manufacturers_name,manufacturers_sort) as temp_name, display_name from manufacturers order by temp_name
Can you give it a go?

Otherwise you might need to use a tempory table or join the table to itself.

Thanks mcjwb that worked beautifuly. I was just about to give up on doing this in the sql command and just put it into an array and do what needed. This is much better. You saved me hours of frustration and work.

I just had to make a minor change
IF(manufacturers_sort=''

I think the other way evaluates to false or unknown all the time.

Thanks guelphdad for the reply but I couldnt get COALESCE() to work for me. The items with an entry for the manufacturers_sort field were still at the bottom.