按ASC列排序，但先使用NULL值? [英] Sort by column ASC, but NULL values first?

问题描述

我需要按日期/时间字段升序对PostgreSQL表进行排序，例如last_updated.

I need to sort a PostgreSQL table ascending by a date/time field, e.g. last_updated.

但是该字段允许为空或null，我希望中具有null的记录出现在之前非null last_updated.
这可能吗?

But that field is allowed to be empty or null and I want records with null in last_updated come before non-null last_updated.
Is this possible?

order by last_updated asc  -- and null last_updated records first ??

推荐答案

Postgres提供了ORDER BY子句的="noreferrer"> NULLS FIRST | LAST 关键字完全满足以下需求:

Postgres provides the NULLS FIRST | LAST keywords for the ORDER BY clause to cater for that need exactly:

... ORDER BY last_updated NULLS FIRST

一个 典型 用例的排序顺序为降序(DESC)，它可以完全替换具有空值的默认升序(ASC)第一的.通常不理想-因此，将空值保留在最后:

A typical use case is with descending sort order (DESC), which yields the complete inversion of the default ascending order (ASC) with null values first. Often not desirable - so, to keep 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可以向后读取btree索引，但是在附加NULL值的地方很重要.

Postgres can read btree indexes backwards, but it matters where NULL values are appended.

这篇关于按ASC列排序，但先使用NULL值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！