sql postgresql greatest-n-per-group window-functions

Aggregate functions are not allowed in FILTER

I have this SQL query:

SELECT
    users.id,
    users.name,
    users.avatar,
    MAX(messages.created_at) max_created_at,
    MAX(messages.body) FILTER (WHERE messages.created_at = MAX(messages.created_at)) last_message,
    CASE WHEN(COUNT(messages.is_read) FILTER (WHERE is_read = false AND messages.from_id != 14) = 0) THEN true ELSE false END is_read,
    COUNT(messages.is_read) FILTER (WHERE is_read = false AND messages.from_id != 14) count_unread
FROM 
    messages 
INNER JOIN 
    users ON messages.from_id = users.id OR messages.to_id = users.id
WHERE 
    (messages.from_id = 14 OR messages.to_id = 14) 
    AND users.id != 14
GROUP BY 
    users.id;

But, this query is showing an error

Aggregate functions are not allowed in FILTER

When I change

MAX(messages.body) FILTER (WHERE messages.created_at = MAX(messages.created_at)) last_message

MAX(messages.body) FILTER (HAVING messages.created_at = MAX(messages.created_at)) last_message

the query is now showing this error

Syntax error at or near "HAVING"

How to fix this?

Solution

Could be done with window functions and DISTINCT ON in a single instance of SELECT:

SELECT DISTINCT ON (u.id)
       u.id, u.name, u.avatar
     , m.created_at  AS max_created_at
     , m.body        AS last_message
     , bool_and(is_read) FILTER (WHERE m.from_id <> 14)          OVER w AS is_read
     , count(*) FILTER (WHERE NOT m.is_read AND m.from_id <> 14) OVER w AS count_unread
FROM   messages m
JOIN   users    u ON u.id IN (m.from_id, m.to_id)
WHERE  14 IN (m.from_id, m.to_id)
AND    u.id <> 14
WINDOW w AS (PARTITION BY u.id)
ORDER  BY u.id, m.created_at DESC NULLS LAST, m.body DESC NULLS LAST;