Orderby if null

Yes, yes. It’s been FOREVER since I’ve posted. I’ve been pretty beat. Not to mention busy. Two reasons: Roxie and my very pregnant sister. It was also just a busy summer with gender reveals and going up to the river. 

I’m posting about this today because I feel like this is a very not well known little mySql knowledge and it really saved me. I’ve got an employee table that has a first name, last name AND nickname. I’m in a situation where the nickname can’t override what’s in the first name field but the nickname does need to show if it’s set. When I try to do a search, I couldn’t get the darn order to work. It would rely more on the first name. For example, if I searched for “an”, it would pull up Tony whose first name is Antone, but would also order it up with the A’s because of his first name. After getting annoyed, I found a nice little command in mySQL called ifnull(). So to call all the employees, 

Select * from employees
ORDER BY ifnull(nickname, first_name) asc

….this would list Tony down with the T’s. You can use this with Laravel as well thanks to orderByRaw().

There’s your tip!

Leave a Reply

Your email address will not be published. Required fields are marked *