meta data for this page
  •  

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. NULL placement in ordered sets

Ordering NULLs 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.

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 the “smallest thing” in sorts since Firebird 2, an expression like NULL < 3 will now also return true. It won't. Using NULL in this kind of expression will always give a NULL outcome.