Postgres CASE в ORDER BY с использованием псевдонима

У меня есть следующий запрос, который отлично работает в 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;

Но я хочу сделать что-то вроде этого:

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;

И я получаю следующую ошибку: ERROR: column "latest_interaction" не существует

Похоже, я не могу использовать псевдоним для агрегированного last_interaction в предложении order by с оператором CASE.

Есть ли обходные пути для этого?

Ответ 1

Попробуйте обернуть его как подзапрос:

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

В руководстве PG указано выражение ORDER BY:

Каждое выражение может быть именем или порядковым номером выходного столбца (элемент списка SELECT) или может быть произвольным выражением, сформированным из значений ввода-столбца.

Решение подзапроса от @Mahmoud будет работать, или вы можете создать ORDER BY, используя исходные столбцы messages.created_at или phone_calls.created_at