Im having a little trouble getting sorting to do what I want...
describe resources; +-------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | title | varchar(64) | | | | | | url | varchar(255) | | | | | | location | varchar(64) | | | | | | category | varchar(128) | | | | | | description | text | | | | | | prefer | char(1) | | | 0 | | | sort_order | int(1) | | | 9 | | | status | char(1) | | | 0 | | | updated | timestamp(14) | YES | | NULL | | | added | timestamp(14) | YES | | NULL | | +-------------+---------------+------+-----+---------+----------------+
My sql is order by sort_oder ASC, added;
At first, I had no value set for the default in sort_order, but those came up first. I am looking to get a ascending sort order that will ignore the reocrd, or basically shove it to the end of the sort if it is empty or null.
sort_order is an INT column. What do you mean by it being "empty"?
Can this be done? Or is my only option in this case to set it to 9 to force those records to the end?
I could not seem to alter the field to get it to change them all to null, so I was unable to test if null is sorted first or last, or ignored altogether.
The behavior has changed for various versions of MySQL, unfortunately:
http://www.mysql.com/doc/en/Problems_with_NULL.html
You can force NULL values to be sorted to either end by adding another sort column:
ORDER BY IF(sort_order IS NULL,1,0), sort_order, added;
That'll sort NULL values at the end. Use IF(sort_ORDER IS NULL,0,1) to sort them at the beginning instead.
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
