CODEWITHSUNDEEP
postgresqlaggregate-functionspostgresql-9.2
tee
tee

Reputation: 4409

Subselect on array_agg in postgresql

Is there a way to use a value from an aggregate function in a having clause in Postgresql 9.2+?

For example, I would like to get each monkey_id with a 2nd highest number > 123, as well as the second highest number. In the example below, I'd like to get (monkey_id 1, number 222).

monkey_id | number
------------------
1         | 222
1         | 333
2         | 0
2         | 444

SELECT 
  monkey_id, 
  (array_agg(number ORDER BY number desc))[2] as second 
FROM monkey_numbers 
GROUP BY monkey_id
HAVING second > 123

I get column "second" does not exist.

Upvotes: 3

Views: 2347

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

You will have to place that in the having clause

SELECT 
  monkey_id
FROM monkey_numbers 
GROUP BY monkey_id
HAVING array_agg(number ORDER BY number desc)[2] > 123

The explanation is that the having will be executed before the select so second still doesn't exist at that time.

Upvotes: 4

Related Questions