Page 1 of 1
MySQL Sorting
Posted: Sun Jan 30, 2011 9:22 pm
by buckit
I have a database with following columns:
customer_last_name, customer_first_name, customer_business_name
I need an option to sort by any of the 3 columns... easy enough. The problem lies with contacts that do not have a business name... there for when you sort by 'contacts_business_name' it puts all of the contacts with that field as NULL at the very top when sorting ascending.
I need the order by to put the NULLs at the END, not the beginning. I have been searching and cant seem to find anything at all related to this. any ideas?
I have tried ORDER BY contacts_business_name != NULL, contacts_business_name, contacts_last_name, contacts_first_name
contacts_business_name != NULL doesn't seem to do anything at all.
Re: MySQL Sorting
Posted: Sun Jan 30, 2011 9:26 pm
by buckit
I do this all the time. post something and as soon as I submit it I figure it out... sometimes it works just to write out the problem to understand it better
anyway... here is my solution:
contacts_business_name != "" DESC, contacts_business_name, contacts_last_name, contacts_first_name
Re: MySQL Sorting
Posted: Sun Jan 30, 2011 9:27 pm
by ale8oneboy
buckit wrote:I have a database with following columns:
customer_last_name, customer_first_name, customer_business_name
I need an option to sort by any of the 3 columns... easy enough. The problem lies with contacts that do not have a business name... there for when you sort by 'contacts_business_name' it puts all of the contacts with that field as NULL at the very top when sorting ascending.
I need the order by to put the NULLs at the END, not the beginning. I have been searching and cant seem to find anything at all related to this. any ideas?
I have tried ORDER BY contacts_business_name != NULL, contacts_business_name, contacts_last_name, contacts_first_name
contacts_business_name != NULL doesn't seem to do anything at all.
This may or may not help ya. But you can control the storing on each individual fields. Example.
ORDER BY customer_last_name ASC, customer_first_name ASC, customer_business_name DESC
Re: MySQL Sorting
Posted: Sun Jan 30, 2011 9:29 pm
by ale8oneboy
buckit wrote:I do this all the time. post something and as soon as I submit it I figure it out... sometimes it works just to write out the problem to understand it better
anyway... here is my solution:
contacts_business_name != "" DESC, contacts_business_name, contacts_last_name, contacts_first_name
Beat me too it. lol Sometimes restating the problem is half the battle.
Re: MySQL Sorting
Posted: Mon Jan 31, 2011 6:20 am
by VladSun
contacts_business_name != NULL doesn't seem to do anything at all.
You can't compare with NULL - you must use IS NULL / IS NOT NULL
http://dev.mysql.com/doc/refman/5.0/en/ ... -null.html
contacts_business_name != ""
is not the same as
contacts_business_name IS NOT NULL
The first one checks for empty string values, while the second one checks for the presence/absence of value.
Re: MySQL Sorting
Posted: Mon Jan 31, 2011 8:12 am
by buckit
VladSun wrote:
contacts_business_name IS NOT NULL
The first one checks for empty string values, while the second one checks for the presence/absence of value.
I know they arent the same. and now I know why I couldnt get NULL to work correctly! Thanks for clearing that up for me! Code has been adjusted!
thank you all for your replies! Can always count on this place when I get stuck
