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
to
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?
2
Answers
I think you require this:
Could be done with window functions and
DISTINCT ON
in a single instance ofSELECT
:Related: