使用别名的ORDER BY中的Postgres CASE [英] Postgres CASE in ORDER BY using an alias

问题描述

我有以下查询在Postgres 9.1中非常有用：

I have the following query which works great in 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;

我得到以下错误：
错误：列 latest_interaction不存在

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

似乎我无法在CASE语句的order by子句中使用别名来表示最新的交互。

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

对此是否有任何解决方法？

Are there any workarounds for this?

推荐答案

尝试将其包装为子查询：

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;

这篇关于使用别名的ORDER BY中的Postgres CASE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！