PostgreSQL：按列排序，具有特定的NON-NULL值LAST [英] PostgreSQL: order by column, with specific NON-NULL value LAST
问题描述
当我发现 NULLS LAST 时，我有点希望在 CASE 中将其推广为'X LAST'。在查询的 ORDER BY 部分中的语句。
When I discovered NULLS LAST, I kinda hoped it could be generalised to 'X LAST' in a CASE statement in the ORDER BY portion of a query.
似乎并非如此。
我试图按两列对表进行排序（简单），但以特定顺序（简单）获得输出，其中一个特定值的一列最后出现（做起来很丑。）。
I'm trying to sort a table by two columns (easy), but get the output in a specific order (easy), with one specific value of one column to appear last (got it done... ugly).
让我们说这些列是 zone ， 状态（不要怪我命名列 zone -我没有给他们命名）。 状态仅接受2个值（ U和 S），而 zone 可以接受约100个值中的任何一个。
Let's say that the columns are zone and status (don't blame me for naming a column zone - I didn't name them). status only takes 2 values ('U' and 'S'), whereas zone can take any of about 100 values.
区域的值的一个子集是（在伪正则表达式中） IN [0-7] Z ，这些在结果中排在第一位。只需使用 Case 即可。
One subset of zone's values is (in pseudo-regexp) IN[0-7]Z, and those are first in the result. That's easy to do with a CASE.
区域也可以取值'Future'，该值应在结果中显示为LAST。
zone can also take the value 'Future', which should appear LAST in the result.
在我典型的糊涂方式中，我只是强加了 CASE 值1000，如下所示：
In my typical kludgy-munge way, I have simply imposed a CASE value of 1000 as follows:
group by zone, status
order by (
case when zone='IN1Z' then 1
when zone='IN2Z' then 2
when zone='IN3Z' then 3
.
. -- other IN[X]Z etc
.
when zone = 'Future' then 1000
else 11 -- [number of defined cases +1]
end), zone, status
这行得通，但这显然是一种矛盾，我想知道是否可能有单线执行
是否有更清洁的方法来达到相同的结果？
This works, but it's obviously a kludge, and I wonder if there might be one-liner doing the same.
Is there a cleaner way to achieve the same result?
推荐答案
Postgres允许 boo 值，因此这是您的 ORDER BY 子句中的精益通用化的'X LAST'：
Postgres allows boolean values in the ORDER BY clause, so here is your generalised 'X LAST':
ORDER BY (my_column = 'X')
表达式的计算结果为布尔值，结果值按以下方式排序：
The expression evaluates to boolean, resulting values sort this way:
FALSE (0)
TRUE (1)
NULL
由于我们处理非空值，所以这就是我们所需要的。这是您的单行代码：
Since we deal with non-null values, that's all we need. Here is your one-liner:
...
ORDER BY (zone = 'Future'), zone, status;
相关：
- Sorting null values after all others, except special
- Select query but show the result from record number 3
- SQL two criteria from one group-by
这篇关于PostgreSQL：按列排序，具有特定的NON-NULL值LAST的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
