On Thu, June 15, 2006 1:14 am, weetat wrote: > I have SQL query , for example , Select country , name from > tbl_chassis order by country. > > The problem of the sql statement is that , if there are empty value > in > country field , it be sorted first . > > How to do sorting the empty value last > ? I can cp() function to do this ? or any mysql function to use? This is really an SQL question, and the "empty" values should properly be called NULL to avoid confusion with '', the empty string... Unless you have '' in your data for country, which is really just Bad Data, imho... The solution for NULL is fairly easy. There is an SQL function called 'coalesce' which will convert any NULL value to, err, whatever you want. So: select coalesce(country, 'ZZZZZZ'), name from ... would probably be the simplest answer. If, however, your country values are '', and if you don't want to fix the application to avoid such a bogus value, you could do: ORDER BY country = '', country, ... country = '' should turn into true/false, which should turn into 0/1 which should order by the ones that have something first, and the ones that are '' last. You may need to do some kind of 'typecast' on the country = '' depending on your SQL engine. You may even be forced to do: select country, nane, typecast_function(country = '', bool) as empty from ... order by empty, country if your SQL engine is particularly baroque (MySQL 3.23, I do believe...) :-) -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
