MySQL Sorting

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

MySQL Sorting

Post 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.
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

Re: MySQL Sorting

Post 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
ale8oneboy
Forum Newbie
Posts: 5
Joined: Wed May 19, 2010 9:53 am

Re: MySQL Sorting

Post 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
ale8oneboy
Forum Newbie
Posts: 5
Joined: Wed May 19, 2010 9:53 am

Re: MySQL Sorting

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL Sorting

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

Re: MySQL Sorting

Post 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 :)
Post Reply