使用别名在ORDER BY中添加Postgres CASE

I have the following query which works great in Postgres 9.1:

我有以下查询在Postgres 9.1中运行良好：

SELECT users.id, GREATEST(
 COALESCE(MAX(messages.created_at), '2012-07-25 16:05:41.870117'),
 COALESCE(MAX(phone_calls.created_at), '2012-07-25 16:05:41.870117')
) AS latest_interaction
FROM users LEFT JOIN messages ON users.id = messages.user_id
LEFT JOIN phone_calls ON users.id = phone_calls.user_id
GROUP BY users.id
ORDER BY latest_interaction DESC
LIMIT 5;

But what I want to do is something like this:

但我想要做的是这样的事情：

SELECT users.id, GREATEST(
 COALESCE(MAX(messages.created_at), '2012-07-25 16:05:41.870117'),
 COALESCE(MAX(phone_calls.created_at), '2012-07-25 16:05:41.870117')
) AS latest_interaction
FROM users LEFT JOIN messages ON users.id = messages.user_id
LEFT JOIN phone_calls ON users.id = phone_calls.user_id
GROUP BY users.id
ORDER BY
  CASE WHEN(
    latest_interaction > '2012-09-05 16:05:41.870117')
  THEN 0
  WHEN(latest_interaction > '2012-09-04 16:05:41.870117')
  THEN 2
  WHEN(latest_interaction > '2012-09-04 16:05:41.870117')
  THEN 3
  ELSE 4
  END
LIMIT 5;

And I get the following error: ERROR: column "latest_interaction" does not exist

并且我收到以下错误：错误：列“latest_interaction”不存在

It seems like I cannot use the alias for the aggregate latest_interaction in the order by clause with a CASE statement.

似乎我不能使用带有CASE语句的order by子句中的聚合latest_interaction的别名。

Are there any workarounds for this?

这有什么变通方法吗？

2 个解决方案

#1

Try to wrap it as a subquery:

尝试将其包装为子查询：

SELECT * 
FROM 
(
    SELECT users.id, 
        GREATEST(
             COALESCE(MAX(messages.created_at), '2012-07-25 16:05:41.870117'),
             COALESCE(MAX(phone_calls.created_at), '2012-07-25 16:05:41.870117')
        ) AS latest_interaction
        FROM users LEFT JOIN messages ON users.id = messages.user_id
        LEFT JOIN phone_calls ON users.id = phone_calls.user_id
        GROUP BY users.id
) Sub
ORDER BY
  CASE WHEN(
    latest_interaction > '2012-09-05 16:05:41.870117')
  THEN 0
  WHEN(latest_interaction > '2012-09-04 16:05:41.870117')
  THEN 2
  WHEN(latest_interaction > '2012-09-04 16:05:41.870117')
  THEN 3
  ELSE 4
  END
LIMIT 5;

#2

The PG manual says the ORDER BY expression:

PG手册说ORDER BY表达式：

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

每个表达式可以是输出列的名称或序号（SELECT列表项），也可以是由输入列值组成的任意表达式。

The sub-query solution from @Mahmoud will work, or you can create the ORDER BY using the original columns messages.created_at or phone_calls.created_at

来自@Mahmoud的子查询解决方案可以使用，或者您可以使用原始列messages.created_at或phone_calls.created_at创建ORDER BY

#1