按 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,我希望 last_updated
中具有 null 的记录 before 非 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 有 NULLS FIRST |
修饰符:ORDER BY
表达式的 LAST
Postgres has the NULLS FIRST | LAST
modifiers for ORDER BY
expression:
... ORDER BY last_updated NULLS FIRST
典型用例是降序排序(DESC
),它产生完全倒置的默认的升序 (ASC
) 首先使用空值 - 这通常是不可取的.最后对 NULL
值进行排序:
... 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 for some query plans it matters where NULL
values are appended. See:
这篇关于按 ASC 列排序,但首先是 NULL 值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!