NULLS First | Last

I thought to write this blog because I found a fantastic and very helpful feature with ORDER BY clause.

Actually I have data which I want to sort by using the Order by clause, but unfortunately the data has lot of NULL values too. And you know it doesn’t look good if we sort values in DESC order and all the null values got displayed on top (by default). While I wish all the null value records should display at last. And as usual as a lazy programmer I don’t want to handle the issue with code.

So what, I did my favorite – Goooooooooogling, and I found a helpful information to share.

We have NULLS First | Last keywords with Order by clause to specify that NULL values should be returned Before | After non-null values. For example:

SELECT * FROM users ORDER BY lastname DESC NULLS LAST;

It make my job done perfectly! Hope this helps you all.

 

How to get the single row having highest column value

Sometimes during interview a question has been asked. That how to get the single row which have certain column value highest or lowest without using any condition. Actually there are certain ways by which we can accomplish this. One way which i want to discuss with all of you is very simple. By using “ORDER BY” and “LIMIT” clause of MySQL.

mysql> SELECT employeeId FROM employees ORDER BY salary DESC LIMIT 1;

The above sql statement will provide us the single `employeeId` from table `employees` who have the highest salary.