Q
Problem solve Get help with specific problems with your technologies, process and projects.

Ordering nulls first, then numbers starting at 0

I have a query that brings back a field that can be nulls or numbers. If I ORDER BY using DESC on this field, I get the nulls at the beginning, but then the numbers start at 999 and work their way down to 000 after the nulls. Is there a way to get the null rows returned first, and then have the numbers start at 000 - 999? Here is an example using three rows. Row 1 is the row I want to have nulls first then numbers after the nulls 000-999. DESC works like this today:

 1    2    3
    |XXXX|ABC
    |XXXX|XYZ
 999|XXXX|XYZ
 001|XXXX|ABC
 000|XXXX|ABC
I would like it to be:
 1    2    3
    |XXXX|ABC
    |XXXX|XYZ
 000|XXXX|ABC
 001|XXXX|ABC
 999|XXXX|XYZ
Is this possible?

Sure, it is possible, you just have to use a little "magic" to get things ordered the way you want to. Your problem lies in the fact that the NULL value lies after all numbers when doing a sort. According to SQL standards, NULL has to be the lowest or the highest value in a sort. It can't be in the middle. And Oracle chooses to implement it as the highest value in the sort. You'd like it to be the opposite. So let's change that!

First, you have to know the range of possible values in your data. I'm going to assume that the range of possible values is 0-999. I've set up a test table with some dummy data as you can see below:

ORA9I SQL> select * from test;

        ID
----------
         5

       993
       104
The second row in the table has a NULL value. Now, if I want this NULL value to appear first in my sorted list, I have to assign a value lower than the minimum in my range in the ORDER BY clause. Since the range is 0-999, any negative number will work. I'll use the NVL function to replace NULL values with a negative number. The following query does what you are seeking:
ORA9I SQL> SELECT * FROM test ORDER BY NVL(id,-1);

        ID
----------

         5
       104
       993
As you can see, I substituted a negative number for any NULL values, only in the ORDER BY clause. I did not perform this substitution in the SELECT clause, so it doesn't show up in my result set. And the NULL column appears first in the list, when it normally appears last by default.

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close