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.
MySQL Sorting
Moderator: General Moderators
Re: MySQL Sorting
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
anyway... here is my solution:
contacts_business_name != "" DESC, contacts_business_name, contacts_last_name, contacts_first_name
-
ale8oneboy
- Forum Newbie
- Posts: 5
- Joined: Wed May 19, 2010 9:53 am
Re: MySQL Sorting
This may or may not help ya. But you can control the storing on each individual fields. Example.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.
ORDER BY customer_last_name ASC, customer_first_name ASC, customer_business_name DESC
-
ale8oneboy
- Forum Newbie
- Posts: 5
- Joined: Wed May 19, 2010 9:53 am
Re: MySQL Sorting
Beat me too it. lol Sometimes restating the problem is half the battle.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
Re: MySQL Sorting
You can't compare with NULL - you must use IS NULL / IS NOT NULLcontacts_business_name != NULL doesn't seem to do anything at all.
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.
There are 10 types of people in this world, those who understand binary and those who don't
Re: MySQL Sorting
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!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.
thank you all for your replies! Can always count on this place when I get stuck