Firebird Documentation Index → Firebird Null Guide → Sorts |
In Firebird 2, NULL
s are considered “smaller” than
anything else when it comes to sorting. Consequently, they come first in ascending sorts and
last in descending sorts. You can override this default placement by adding a NULLS
FIRST or NULLS LAST directive to the ORDER
BY clause.
In earlier versions, NULL
s were always placed at the end of a sorted
set, no matter whether the order was ascending or descending. For Firebird 1.0, that was the end
of the story: NULL
s would always come last in any sorted set, period.
Firebird 1.5 introduced the NULLS FIRST/LAST syntax, so you could force
them to the top or bottom.
To sum it all up:
Table 6. NULL
s placement in ordered columns
Ordering | NULL s
placement
|
||
---|---|---|---|
Firebird 1 | Firebird 1.5 | Firebird 2 | |
order by Field [asc] | bottom | bottom | top |
order by Field desc | bottom | bottom | bottom |
order by Field [asc | desc] nulls first | — | top | top |
order by Field [asc | desc] nulls last | — | bottom | bottom |
Specifying NULLS FIRST on an ascending or NULLS LAST on a descending sort in Firebird 2 is of course rather pointless, but perfectly legal. The same is true for NULLS LAST on any sort in Firebird 1.5.
If you override the default NULL
s placement, no index will be
used for sorting. In Firebird 1.5, that is the case with NULLS FIRST.
In 2.0 and higher, with NULLS LAST on ascending and NULLS
FIRST on descending sorts.
If you open a pre-2.0 database with Firebird 2, it will show the
old NULL
ordering behaviour (that is: at the
bottom, unless overridden by NULLS FIRST). A
backup-restore cycle will fix this, provided that at least the restore is executed with
Firebird 2's gbak!
Firebird 2.0 has a bug that causes the NULLS FIRST|LAST directive to fail under certain circumstances with SELECT DISTINCT. See the bugs list for more details.
Don't be tempted into thinking that, because NULL
is now the
“smallest thing” in sorts, an expression like “NULL
< 3” will return true
in Firebird 2 and up. It won't. Using
NULL
in this kind of expression will always give a
NULL
outcome.
Firebird Documentation Index → Firebird Null Guide → Sorts |