Archive for July, 2009

By default, null values are put on top of the query resultset when field is ordered by in ascendant form.

This comes to be a problem in many scenarios, specially when we are ordering by a position field that can contain an integer value for its position on the dataset or null if position is not defined. Rows that have undefined position have lower weight than the specified ones thus coming first.

The following SQL query is from a very common scenario that represents a SELECT to fetch all city registries in “importance” (most common, not in fact important cities – don’t get mad if you live in an odd city) order.

SELECT
  id, city
FROM
  cities
ORDER BY
  POSITION;

This brings us all null-valued position rows first and not null positioned in ascending order, at the bottom.

This happens because our ordering pool will look like the following:
position (integer or null), city field value (string)

So null values are considered smaller than 1 (lowest positive integer) and then comes first in our resultset.
Read the full article