Sorts

In Firebird 2, NULLs 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, NULLs 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: NULLs 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. NULLs placement in ordered columns

Ordering	`NULL`s placement
Ordering	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.

Notes

If you override the default NULLs 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.

Warning

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.