Menu
Is there a way to set the default order used by Oracle to be NULL LAST (or NULL FIRST), without have to put it in each query?
835
If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.
If the null ordering is not specified then the handling of the null values is: - NULLS LAST if the sort is ASC - NULLS FIRST if the sort is DESC - If neither ascending nor descending order is specified, and the null ordering is also not specified, then both defaults are used and thus the order will be ascending with ...
By default, the ORDER BY clause sorts rows in ascending order whether you specify ASC or not. If you want to sort rows in descending order, you use DESC explicitly. NULLS FIRST places NULL values before non-NULL values and NULLS LAST puts the NULL values after non-NULL values.
using ORDER BY NULL is a workaround that satifies the syntax requirement but does not actually change the order of the data. In effect it is an instruction to not order at all. N.B.: some (myself included) prefer to use SELECT 1 instead of SELECT NULL but there is no difference in effect.
No, there is no way to change the default behavior of NULLS FIRST and NULLS LAST:
NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.
I can't prove it's impossible to change, but I cannot find such a feature in the places it is most likely to be implemented.
SQL Option The manual does not mention anything.
Parameter None of the nls parameters in V$PARAMETER control it: select * from v$parameter where name like '%nls%';
Hidden Parameter There's no hidden parameter. I tried searching for anything like %null% or %sort%, none of them appear relevant.
Locale Builder Oracle allows you to create your own custom sorting. It has a lot of options, but none of them allow you to define how NULLs are sorted. Setting the Major Sort and Minor Sort numbers really high or low does not change it (I was hoping that a NULL was implemented as a hard-coded small or large value). You can set the sort order for 0x0000, which is "NULL", but that's a different type of NULL.
151
No, there is no way to enable default ordering without using order by clause, which allows you put NULLs last or first. Here is an example:
Ascending ordering
SQL> with t1(col) as(
2 select 1 from dual union all
3 select 2 from dual union all
4 select null from dual union all
5 select 3 from dual
6 )
7 select *
8 from t1
9 order by col asc nulls last
10 ;
Result:
COL
------
1
2
3
null
Descending ordering
SQL> with t1(col) as(
2 select 1 from dual union all
3 select 2 from dual union all
4 select null from dual union all
5 select 3 from dual
6 )
7 select *
8 from t1
9 order by col desc nulls last
10 ;
Result:
COL
----------
3
2
1
null
ORDER BY EMPLOYER_NAME DESC NULLS LAST
The above worked for me on Oracle 11g
3
answered Oct 19 '22 06:10
In ascending order, NULL values will always be sorted last
2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
