?

Log in

No account? Create an account

Dealing with null values with "ORDER BY ... DESC"

« previous entry | next entry »
Dec. 13th, 2006 | 10:29 am
mood: satisfiedsatisfied
music: Whirring fans

In postgres, but this has equivalents in other DBMS.

If you do an ascending sort, postgres will place all null values at the end of the sort order. This is usually what you want. But if you do a descending sort, all null values will be at the start of the sort order. This is usually NOT what you want.

Some typical examples would be sorting by price from highest to lowest, sorting by area from highest to lowest. You may wish to keep data with null values as there is other associated data of interest, rather than simply removing it with WHERE column IS NOT NULL. As an example, you might want items with unknown price to be listed last rather than not listed at all.

One solution is like this (the question arose out of a left join producing null values in the joined table):

SELECT * FROM t1 LEFT JOIN t2
ORDER BY t2.col DESC


Here we will get the null values first. But,

SELECT * FROM t1 LEFT JOIN t2
ORDER BY COALESCE(t2.col, 0) DESC


Here we will get the null values last, providing 0 is less than all other values in that column. It might be necessary to use negative values, or the limit value for that particular data type.

What COALESCE does is it returns the first non-null argument provided to it. Documentation here. Some DBMS have a function IFNULL with equivalent functionality. Either one can be replaced with a CASE, at the cost of looking considerably messier.

Link | Leave a comment | Share

Comments {0}