Why Do Null Values Come First When Ordering Desc in a Postgresql Query

Why do NULL values come first when ordering DESC in a PostgreSQL query?

Actually, with default sort order (ASCENDING) NULL values come last.

Logic dictates that the sort order be reversed with the DESCENDING keyword, so NULLs come first in this case.

But the best part comes last: you can choose which way you want it:

• Use the NULLS FIRST | LAST clause.

Quoting the current manual, version 9.3 as of writing:

If NULLS LAST is specified, null values sort after all non-null
values; if NULLS FIRST is specified, null values sort before all
non-null values. If neither is specified, the default behavior is
NULLS LAST when ASC is specified or implied, and NULLS FIRST when DESC
is specified (thus, the default is to act as though nulls are larger
than non-nulls). When USING is specified, the default nulls ordering
depends on whether the operator is a less-than or greater-than operator.

Bold emphasis mine.

Sort by column ASC, but NULL values first?

Postgres has the NULLS FIRST | LAST modifiers for ORDER BY expression:

... ORDER BY last_updated NULLS FIRST

The typical use case is with descending sort order (DESC), which produces the complete inversion of the default ascending order (ASC) with null values first - which is often not desirable. To sort NULL values last:

... ORDER BY last_updated DESC NULLS LAST

To support the query with an index, make it match:

CREATE INDEX foo_idx ON tbl (last_updated DESC NULLS LAST);

Postgres can read btree indexes backwards, but for some query plans it matters where NULL values are appended. See:

• Performance impact of view on aggregate function vs result set limiting

order by nulls first in descending order in laravel

In an ascending sort, null values appear last by default (and first in a descending sort). Postgres provide a way to override the default sort ordering null with option nulls first and nulls last.

You could use it with orderByRaw:

Source::orderByRaw('last_rank_update nulls first')

Sort NULL values to the end of a table

NULL values are sorted last in default ascending order. You don't have to do anything extra.

The issue applies to descending order, which is the perfect inverse and thus sorts NULL values on top.

PostgreSQL 8.3 introduced NULLS LAST:

ORDER BY somevalue DESC NULLS LAST

For PostgreSQL 8.2 and older or other RDBMS without this standard SQL feature:

ORDER BY (somevalue IS NULL), somevalue DESC

FALSE sorts before TRUE, so NULL values come last, just like in the example above.

See:

• Sort by column ASC, but NULL values first?
• The manual on SELECT

Sorting NULL values in PostgreSQL

Use:

select ...
from ...
order by the_column nulls last;

Look at documentation.

order by nulls first in descending order

Yup, I think you're looking for:

Book.order('ordering ASC NULLS FIRST, created_at DESC')

This will sort by ordering, and then by created_at in results that have the same ordering.

How to change default null sorting behavior from PostgreSQL in the Django ORM

I figured out a way that accommodates DB engines that work either way (null as highest or lowest value) by using extra, making the null check a boolean, and when sorting booleans false < true seems to be universal:

qs = qs.extra(select={'null_start': "publish_start is null"},
              order_by=['null_start', '-publish_start'])

Peewee: how to do ORDER BY ... NULLS (FIRST|LAST)?

I think you're over-complicating the issue, to be honest.

As of Peewee 3.x, you can specify the handling of nulls:

MyModel.select().order_by(MyModel.something.desc(nulls='LAST'))

You can also use a case statement to create an aliased column containing a 1 or 0 to indicate whether the column you're sorting on is null. Then use that alias in the order by.

---

Related Topics