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|ABCI would like it to be:
1 2 3 |XXXX|ABC |XXXX|XYZ 000|XXXX|ABC 001|XXXX|ABC 999|XXXX|XYZIs 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 104The 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 993As 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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.